MySQL BLOB

Summary: in this tutorial, you will learn about MySQL BLOB data type and its applications.

Introduction to MySQL BLOB data type

In MySQL, a BLOB (Binary Large Object) is a data type that allows you to store large binary data, such as images, audio, video, and so on. BLOBs are useful when you want to store and retrieve data in your database.

MySQL supports the following types of BLOBs:

TINYBLOB: Maximum length of 255 bytes.
BLOB: Maximum length of 65,535 bytes.
MEDIUMBLOB: Maximum length of 16,777,215 bytes.
LONGBLOB: Maximum length of 4,294,967,295 bytes.

When you create tables that store BLOBs, you select the appropriate BLOB type based on the size of binary data you plan to store.

MySQL BLOB data type example

First, create a table that includes a BLOB column:

CREATE TABLE images (
   id INT PRIMARY KEY AUTO_INCREMENT,
   title VARCHAR(255) NOT NULL,
   image_data LONGBLOB NOT NULL
);Code language: PHP (php)

Second, show the value of the secure_file_priv variable:

SELECT @@secure_file_priv;Code language: CSS (css)

Output:

+------------------------------------------------+
| @@secure_file_priv                             |
+------------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The secure_file_priv system variable restricts the locations on the MySQL server from which the LOAD_FILE() function can read files.

If you attempt to load the file from other locations, the LOAD_FILE() function returns NULL.

Third, insert a binary image into the image_data of the images table using the LOAD_FILE() function:

INSERT INTO images (title,image_data) 
VALUES ('MySQL tutorial', LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/logo.png'));Code language: JavaScript (javascript)

Make sure to replace C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/logo.png with the actual absolute path to your image file.

Notice that you need to replace the backslash (\) with the forward-slash (/) in the path to the file on Windows to make it work properly.

Finally, retrieve the binary data from the BLOB column:

SELECT * FROM images;

In practice, you often use applications written in Python, PHP, Java, and so on to read files in the client and store them in the BLOB column:

Summary

Use MySQL BLOB to store large binary data in the database.

Was this tutorial helpful?