How to Store Image in MySQL Database

In this tutorial, we aim to understand how to store images in a MySQL database.

Standard SQL uses BLOB (Binary Large Object) data types to store large amounts of data.

A sequence of bytes or octets defines a binary string, typically used to store large images or media files such as audio and video data in the MySQL database. The BLOB value is flexible to write it as binary or string values as per the requirement.

Let us try to understand how this works.

To begin with, we create a table named student_details with the stu_photograph column. Here, the photograph of the image would be stored in the stu_photograph column.

-- Using the following table as an example:
CREATE TABLE stu_information(
 	stu_photograph BLOB
);

Now let us insert student photograph in the table with stu_photograph acting as the column wherein the image needs to be placed.

-- This will insert a file in a BLOB column.
INSERT INTO stu_information (stu_photograph) VALUES(LOAD_FILE('/image_path/image_fileName.png'));

The code above would enter the student data in the table student_details. We can visualize this table with the following command:

select * from stu_information;

The above stated code block would generate the following output:

As we can see above, the stu_photograph column has been created with BLOB data support to store images.

We can grant the FILE privilege by logging into the root and executing the following command:

GRANT FILE ON *.* TO 'mysql_user'@'localhost';