Author: Francis Mwangi NdunguLast Updated: Fri, Oct 2, 2020
BLOB is an acronym for Binary Large Object. This data-type stores binary information in a MySQL database(e.g., images, PDF, and multimedia files). Use BLOB instead of using a file-system to store images and referencing the path in your database when developing applications that require tightly-coupled data. For instance: an employee portal, light-weight e-commerce websites, school databases, and bank-related applications.
When using a BLOB data type in an application, you should create a specific column to hold the binary data (e.g., a student's passport in a column named
Using BLOB data-type allows your application to have a central repository for all related information, making maintenance and database backup easier. Another advantage is database integrity. Use MySQL transactions when users enter data into your application to check consistency at the database level.
In this guide, you'll use PHP to create a sample project that Uses MySQL BLOB data type to store students' information, including photos in a central database.
Before you begin, ensure you have the following:
First, SSH to your server. Then, download some samples images we generated from This Person Does Not Exist to use in your database and save them to the
/var/lib/mysql-files directory. Please note, this is a default read directory defined by the
secure_file_priv system variable.
$ sudo wget -O /var/lib/mysql-files/mary.png https://ewr1.vultrobjects.com/vultr-docs/5743/mary.png $ sudo wget -O /var/lib/mysql-files/john.png https://ewr1.vultrobjects.com/vultr-docs/5743/john.png $ sudo wget -O /var/lib/mysql-files/baby.png https://ewr1.vultrobjects.com/vultr-docs/5743/baby.png
Next, log in to the MySQL server.
$ sudo mysql -u root -p
When prompted, enter the root password for your MySQL server and press ENTER to proceed.
mysql> CREATE DATABASE students;
Select the database.
mysql> USE students;
Create a students'
register table. For this project, you'll use the
MEDIUMBLOB data type, which can store up to 16 MB of binary data.
mysql> CREATE TABLE register ( student_id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), photo MEDIUMBLOB );
Then, add some sample students to the
register table by running the commands below one by one. The
LOAD_FILE function retrieves the images you've downloaded using
wget and converts them to a format understandable by the MySQL
BLOB column defined as
mysql> INSERT INTO register (first_name, last_name, photo) VALUES ('MARY', 'ROE', LOAD_FILE("/var/lib/mysql-files/mary.png")); mysql> INSERT INTO register (first_name, last_name, photo) VALUES ('JOHN', 'DOE', LOAD_FILE("/var/lib/mysql-files/john.png")); mysql> INSERT INTO register (first_name, last_name, photo) VALUES ('BABY', 'ROE', LOAD_FILE("/var/lib/mysql-files/baby.png"));
register table to ensure that the records were inserted successfully. Since the BLOB column lists a large string of characters, use the MySQL
LEFT keyword to get the first 10 characters, which is sufficient to show that the
photo column has some data.
mysql> SELECT student_id, first_name, last_name, HEX(LEFT(photo, 10)) from register;
You should get the output shown below if you've followed the instructions correctly.
+------------+------------+-----------+----------------------+ | student_id | first_name | last_name | HEX(LEFT(photo, 10)) | +------------+------------+-----------+----------------------+ | 1 | MARY | ROE | 89504E470D0A1A0A0000 | | 2 | JOHN | DOE | 89504E470D0A1A0A0000 | | 3 | BABY | ROE | 89504E470D0A1A0A0000 | +------------+------------+-----------+----------------------+ 3 rows in set (0.00 sec)
Close the MySQL console.
Next, create a PHP script in the root folder of your website to connect to the MySQL database using PDO(PHP Data Objects) and retrieve the records you've added above. The script will also display each student's information and photo in an HTML web page.
For convenience, download the example script from GitHub here:
$ sudo wget -O /var/www/html/students.php https://raw.githubusercontent.com/vultr/vultr-docs/master/mysql-and-mariadb/using-mysql-blob-data-with-php-on-ubuntu-20-04/students.php
Open the file for editing.
$ sudo nano /var/www/html/students.php
Update the database variables with your database name, user, password, and host. Look for this section:
$db_name = 'students'; $db_user = 'sample_user'; $db_password = 'SAMPLE_PASSWORD'; $db_host = 'localhost';
Save and close the file by pressing CTRL + X, Y then ENTER.
Next, visit the URL below and remember to replace
public_ip_address with the public IP address or domain name of your website.
You should see an HTML web page with the records you've created in the database, including the students' photos, as shown below.
As you can see from this guide, MySQL BLOB data type is advantageous in applications that require binary data to be stored together with the associated information in a single database. This approach for storing binary data is excellent for light-weight applications.