Mysql 简明教程
MySQL - BLOB
许多用户应用程序需要存储不同类型的数据，包括文本、图像、文件等。在 MySQL 数据库中使用 BLOB 允许您将所有这些类型的数据存储在同一个数据库中，消除了对单独文件系统的需求。
Many user applications require the storage of different types of data, including text, images, files, and more. Using BLOBs in a MySQL database allows you to store all these types of data within the same database, eliminating the need for a separate file system.
The MySQL BLOB Data Type
MySQL BLOB（二进制大对象）数据类型用于存储二进制数据，例如图像、音频、视频或任何其他类型的二进制文件。BLOB 列可以存储可变长度的二进制数据，这使其适用于处理各种大小的文件。
The MySQL BLOB (Binary Large Object) data type is used to store binary data, such as images, audio, video, or any other type of binary file. BLOB columns can store variable-length binary data, making it suitable for handling files of various sizes.
考虑一个通过表单收集用户信息的应用程序。此信息可能包括姓名和地址等个人详细信息，以及 PAN 卡或 AADHAR 卡等图像证明。您可以将这些文件存储为 MySQL 数据库中的 BLOB，而不是在文件系统中单独管理它们。
Consider an application that collects user information through forms. This information may include personal details, such as name and address, along with image proofs like PAN cards or AADHAR cards. Instead of managing these files separately in a file system, you can store them as BLOBs in a MySQL database.
Syntax
以下是为表字段分配 BLOB 数据类型的基本语法 -
Following is the basic syntax to assign BLOB data type on a table field −
CREATE TABLE table_name (column_name BLOB,...)Example
让我们考虑一个基本示例，以显示如何为表字段分配 BLOB 数据类型。在这里，我们正在创建一个名为“demo_table”的表，其中包含两个字段“ID”和“DEMO_FILE” -
Let us consider a basic example to show how to assign BLOB datatype to a table field. Here, we are creating a table named 'demo_table' with two fields "ID" and "DEMO_FILE" −
CREATE TABLE demo_table (
ID INT NOT NULL,
DEMO_FILE BLOB
);以下是所获得的输出 −
Following is the output obtained −
Query OK, 0 rows affected (0.01 sec)您可以使用以下命令查看表结构：
You can see the table structure with the following command −
DESC demo_table;获得的表如下 −
The table obtained is as follows −
Inserting Data into BLOB Fields
您可以在文件中加载到 BLOB 字段中，从而将一些值插入到数据库表中。但是，在这样做之前，请确保满足以下条件：
You can insert some values into a database table, by loading a file to the BLOB field using the LOAD_FILE() function. However, before doing so, ensure that the following conditions are met −
-
*File Existence −*The file you want to insert must exist on the MySQL server host location. To determine the required location, you can use the secure_file_priv variable with the following command. If the result of this command is not empty, the file to be loaded must be located in that specific directory.
-
Specify Full File Path − When using the LOAD_FILE() function, you must pass the full path of the file as an argument, like '/users/tutorialspoint/file_name.txt'. For Windows users, remember to use double backslashes as escape characters in the path ('//users//tutorialspoint//file_name.txt').
-
Check 'max_allowed_packet' Value − MySQL Server has a max_allowed_packet variable that determines the maximum allowed file size for loading. To check the value of this variable, you can use the following command −
-
Grant FILE Privileges − Make sure the MySQL user account has FILE privileges granted. To grant file privileges to a user, you can use the following command (usually performed by a user with administrative privileges, such as 'root') −
-
File Readability − Lastly, make sure that the file is readable by the MySQL server.
Example
要将值插入到先前创建的表“demo_table”中，可以使用以下 INSERT 查询：
To insert values into a previously created table 'demo_table', you can use the following INSERT query −
INSERT INTO demo_table
VALUES(1,
LOAD_FILE("C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\sample.txt"));要验证插入，可以使用以下查询从“demo_table”中检索数据：
To verify the insertion, you can retrieve the data from the 'demo_table' using the following query −
SELECT * FROM demo_table;我们可以在下面的输出中看到，表包含“sample.txt”文件中内容的十六进制字符串。您可以将任何类型的文件加载到 MySQL 中，例如图像、多媒体文件、PDF 文档等。
We can see in the output below, the table contains the hex string of content present in the 'sample.txt' file. You can load any type of files into MySQL, like images, multimedia files, PDF documents etc. −
Types of BLOB Datatype
MySQL 提供了四种类型的 BLOB 数据类型，每种类型都具有不同的最大存储容量。虽然它们都用于存储二进制数据（例如图像或文件），但它们所能容纳的对象的最大大小不同。以下是四种 BLOB 数据类型：
MySQL provides four types of BLOB datatypes, each with varying maximum storage capacities. While they all serve the same purpose of storing binary data, such as images or files, they differ in the maximum size of objects they can accommodate. Here are the four BLOB datatypes −
-
TINYBLOB − It can store a maximum of 255 bytes, or 255 characters.
-
BLOB − It can store up to 65,535 (216 - 1) bytes, which is equivalent to 64KB of data.
-
MEDIUMBLOB − It can store up to 16,777,215 (224 - 1) bytes, or 4GB.
-
LONGBLOB − It is the largest among these datatypes and can store objects up to 4,294,967,295 bytes (232 - 1), or 4GB.
让我们尝试创建所有上述 BLOB 数据类型类型的表。
Let us try to create tables with all types of BLOB datatypes mentioned above.
Creating a Table with TINYBLOB Datatype
在此示例中，我们在字段上使用 TINYBLOB 数据类型创建名为“demo_tinyblob”的表：
In this example, we are creating a table named 'demo_tinyblob' with TINYBLOB datatype on a field −
CREATE TABLE demo_tinyblob (ID INT, DEMO_FIELD TINYBLOB);Verification
您可以使用以下命令查看表结构：
You can see the table structure with the following command −
DESC demo_tinyblob;获得的表如下 −
The table obtained is as follows −
Creating a Table with MEDIUMBLOB Datatype
在这里，我们使用以下查询创建一个表，名为“demo_mediumblob”，其中包含 MEDIUMBLOB 类型的字段：
Here, we are creating a table named 'demo_mediumblob' with a field of type MEDIUMBLOB using the following query −
CREATE TABLE demo_mediumblob (ID INT, DEMO_FIELD MEDIUMBLOB);Verification
您可以使用以下命令查看表结构：
You can see the table structure with the following command −
DESC demo_mediumblob;以下是要获得的表：
Following is the table obtained −