How PDO operates big data objects in MySQL

PDO operates big data objects

Generally in the database, we only save int and varchar type data. First, because modern relational databases have a lot of optimizations for these contents. Second, most indexes cannot be applied to fields with too much content. For example, text type fields are not suitable for creating indexes. Therefore, when we use a database, we rarely store large content fields in the database. However, MySQL actually prepares this type of storage for us, but we don’t usually use it much.

What is a Big Data Object

"Large" usually means "approximately 4kb or more", although some databases can easily handle up to 32kb of data. Large objects may be text or binary in nature, and we can make PDO use large data types by using the PDO::PARAM_LOB type code in the PDOStatement::bindParam() or PDOStatement::bindColumn() call. PDO::PARAM_LOB tells PDO to map the data as a stream so that it can be manipulated using the PHP Streams API.

In MySQL, setting the field type to blob means that the field is in large object format. When using bindParam() or bindColumn(), if the parameter of the specified field is of type PDO::PARAM_LOB, you can directly obtain the content of this object in the form of a handle and continue to operate on it just like fopen().

<code>CREATE TABLE `zy_blob` (<br>  `id` int(11) NOT NULL AUTO_INCREMENT,<br>  `attach` longblob,<br>  PRIMARY KEY (`id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;<br></code>

This is a data table for our testing. The attach field is set to longblob type, which is a relatively large blob type, so that we can store more information. After all, today's pictures or files can easily start from a few MB or dozens of MB. We directly use the largest blob type for a simple test. The size of tinyblob is 255 bytes, the size of blob type is 65k, mediumblob is 16M and longblob is 4G.

What will happen if we directly operate big data objects?

Let’s simply directly operate the big data object to see what the result is.

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

We directly store the file opened by fopen() into the blob field without binding the field first. It can be observed from the database that the blob-related fields only store strings in the form of "Resource id #6". In other words, without any processing, the $fp handle is forced to a string type, and the result of the handle type being forced is that only a resource ID will be output, and the blob will only be the same as the character type field. This string is just recorded.

Correct posture

Next let’s take a look at the correct posture, which is to insert data through bindParam() and read data through bindColumn().

<code>$stmt = $pdo->prepare("insert into zy_blob (attach) values (?)");<br><br>$fp = fopen('4960364865db53dcb33bcf.rar', 'rb');<br><br>$stmt->bindParam(1, $fp, PDO::PARAM_LOB); // 绑定参数类型为 PDO::PARAM_LOB<br>$stmt->execute();<br><br>$stmt = $pdo->prepare("select attach from zy_blob where id=2");<br>// // $file = $stmt->fetch(PDO::FETCH_ASSOC);<br>// // print_r($file); // 空的<br>$stmt->execute();<br>$stmt->bindColumn(1, $file, PDO::PARAM_LOB); // 绑定一列到一个 PHP 变量<br>$stmt->fetch(PDO::FETCH_BOUND); // 指定获取方式，返回 TRUE 且将结果集中的列值分配给通过 PDOStatement::bindParam() 或 PDOStatement::bindColumn() 方法绑定的 PHP 变量<br>print_r($file); // 二进制乱码内容<br>$fp = fopen('a.rar', 'wb');<br>fwrite($fp, $file);</code>

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

The above is the detailed content of How PDO operates big data objects in MySQL. For more information, please follow other related articles on the PHP Chinese website!