Shulou(Shulou.com)05/31 Report--

This article mainly introduces the method of operating big data object in PDO, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

PDO manipulates big data object

Generally speaking, in the database, we only save the data of int and varchar types, first, because modern relational databases have a lot of optimizations for these contents, and second, most indexes cannot be imposed on fields with too much content, for example, fields of text type are not suitable for creating indexes. Therefore, when we use a database, we rarely store large content fields in the database. However, MySQL actually provides this type of storage for us, but we don't usually use it much. Today we will learn how to use PDO to manipulate big data objects in MySQL.

What is big data's object?

"large" usually means "about 4kb or more", although some databases can easily handle data as much as 32kb before the data reaches "large". Large objects may be textual or binary in nature, and we use PDO::PARAM_LOB type codes in PDOStatement::bindParam () or PDOStatement::bindColumn () calls to make PDO use the big data type. PDO::PARAM_LOB tells PDO to map the data as a stream so that it can be manipulated using PHP Streams API.

For MySQL, setting the field type to blob is a field in large object format. In the case of bindParam () or bindColumn (), if you specify that the parameter of the field is of type PDO::PARAM_LOB, you can get the contents of the object directly in the form of a handle and continue to operate on it just like fopen ().

CREATE TABLE `zy_ blob` (`id` int (11) NOT NULL AUTO_INCREMENT, `attach` longblob, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

This is a data table we used to test. Set the attach field to the longblob type, that is, the larger blob type, so that we can store more information. After all, today's pictures or files casually start with a few meters or dozens of meters, so we simply use the largest blob type for simple testing. The size of tinyblob is 255 bytes, the size of blob type is 65k, the size of mediumblob is 16m, and the size of longblob is 4G.

What happens if you directly manipulate the object of big data?

First of all, let's simply manipulate the big data object directly to see what the result is.

$stmt = $pdo- > prepare ("insert into zy_blob (attach) values (?)"); $fp = fopen ('4960364865db53dcb33bcf.rarfolk,' rb'); $stmt- > execute ([$fp]); $stmt = $pdo- > query ("select attach from zy_blob where id=1"); $file = $stmt- > fetch (PDO::FETCH_ASSOC); print_r ($file); / / Array// (/ / [attach] = > Resource id # 6max /)

In this code, we don't bind the field, and then store the file opened by fopen () directly into the blob field. As you can see, the blob-related fields in the database only store strings such as Resource id # 6. That is, without any processing, the $fp handle is cast to a string type, and the result of the overturn of the handle type is that only one resource ID is output, and the blob simply records the string like a field of character type.

The correct posture

Next let's look at the correct pose, that is, insert the data through bindParam () and read the data through bindColumn ().

$stmt = $pdo- > prepare ("insert into zy_blob (attach) values"); $fp = fopen ('4960364865db53dcb33bcf.rarbread,' rb'); $stmt- > bindParam (1, $fp, PDO::PARAM_LOB); / / binding parameter type is PDO::PARAM_LOB$stmt- > execute (); $stmt = $pdo- > prepare ("select attach from zy_blob where id=2"); / / $file = $stmt- > fetch (PDO::FETCH_ASSOC); / print_r ($file) / / empty $stmt- > execute (); $stmt- > bindColumn (1, $file, PDO::PARAM_LOB); / / bind a column to a PHP variable $stmt- > fetch (PDO::FETCH_BOUND); / / specify the fetch method, return TRUE and assign the column values in the result set to the PHP variable print_r ($file) bound by the PDOStatement::bindParam () or PDOStatement::bindColumn () method / / binary garbled content $fp = fopen ('a.rarounds,' wb'); fwrite ($fp, $file)

First, after we bind the data through bindParam () and specify the PDO::PARAM_LOB type, we normally insert the handle binary contents of the file into the database. Next, we use bindColumn () and also specify the PDO::PARAM_LOB type to get the queried data. Print the query field information directly, and you can see that it is a binary type content. Finally, we save the binary content to a file with another name.

You can replace the contents of the file above, and then execute the code to see if the final generated file is the same as the original file. What I am using here is a compressed package file, and the resulting a.rar file is exactly the same as the original file size and the unzipped content.

Summary

What on earth is the operation of big data object? In fact, it is the large file that we usually have to save. We read these files into the program as a binary stream, and then save them in the fields of the database. Think about the image preservation that we usually use most in development can be done with this. However, we can highlight here, we recommend that you save the files directly in the file directory, and only save their path in the database. Database resources are precious, the larger the table is, the less conducive to optimization, and the database itself has a caching mechanism, so it is not worth the loss to waste its resources to save such large files. Of course, if there are some special needs, such as some private files do not want to be saved directly in the hard disk file directory, or as a temporary cross-server storage solution.

In modern development, I believe your company will not be too stingy to buy a cloud storage (Qiniu, upyun, Aliyun OSS). They can not only be used as a memory or network disk, but also have more functions, such as image clipping, watermarking, gifted CDN, bandwidth, traffic, and so on. In short, we should try our best to go to the cloud for modern storage. Even if it is personal development, many manufacturers will provide free use in the case of small traffic and small amount of data, which is much more convenient than ourselves.

Thank you for reading this article carefully. I hope the article "how to manipulate big data objects in PDO" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.