How PDO operates big data objects

This article will give you a detailed introduction to the method of PDO operating big data objects. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

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, and secondly, 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. Today we will learn how to use PDO to operate big data objects in MySQL.

What is a Big Data Object

"Large" usually means "approximately 4kb or more", although some databases can easily store data before it reaches "large" Handles data up to 32kb. 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.

For MySQL, setting the field type to blob is a field 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().

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 for our testing. The attach field is set to longblob type, which is a larger 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 first simply directly operate the big data object to see what the result is.

$stmt = $pdo->prepare("insert into zy_blob (attach) values (?)");
$fp = fopen('4960364865db53dcb33bcf.rar', '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 #6
// )

In this code, we do not bind the field, and then directly store the file opened by fopen() into the blob field. It can be seen that in the database, the blob-related fields only store strings such as 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().

$stmt = $pdo->prepare("insert into zy_blob (attach) values (?)");

$fp = fopen('4960364865db53dcb33bcf.rar', 'rb');

$stmt->bindParam(1, $fp, PDO::PARAM_LOB); // 绑定参数类型为 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); // 空的
$stmt->execute();
$stmt->bindColumn(1, $file, PDO::PARAM_LOB); // 绑定一列到一个 PHP 变量
$stmt->fetch(PDO::FETCH_BOUND); // 指定获取方式，返回 TRUE 且将结果集中的列值分配给通过 PDOStatement::bindParam() 或 PDOStatement::bindColumn() 方法绑定的 PHP 变量
print_r($file); // 二进制乱码内容
$fp = fopen('a.rar', 'wb');
fwrite($fp, $file);

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.

You can replace the content of the above file 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 finally generated a.rar file is exactly the same size as the original file and the decompressed content.

Summary

What exactly do big data objects operate on? In fact, it is a large file that we usually save. After we read these files into the program as binary streams, we save them in fields in the database. Thinking about saving the most pictures we use in daily development, we can use this to do it. However, the important point can be drawn here. We recommend that you save the files directly in the file directory and only save their paths in the database. Database resources are precious. The larger the table, the less conducive it is to optimization. Moreover, the database itself has a caching mechanism. It is not worth the gain to waste its resources to save such large files. Of course, if there are some special needs, for example, some private files do not want to be saved directly in the hard disk file directory, or it can be used as a temporary cross-server storage solution.

在现代开发中，相信你的公司也不会吝啬到不去买一个云存储（七牛、upyun、阿里云OSS）。它们不仅仅是能够做为一个存储器、网盘，而是有更多的功能，比如图片的裁剪、水印，赠送的 CDN 、带宽 、 流量之类的，总之，现代的存储大家还是尽量上云吧，即使是个人开发，也有不少厂商会提供小流量小数据量情况下的免费使用，这个都比我们自己来要方便很多。

测试代码：

https://github.com/zhangyue0503/dev-blog/blob/master/php/202008/source/PDO%E6%93%8D%E4%BD%9C%E5%A4%A7%E6%95%B0%E6%8D%AE%E5%AF%B9%E8%B1%A1.php

相关推荐：《mysql教程》

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