How to Work with BLOB in MySQL Database Hosted on Alibaba Cloud

By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

BLOB is an acronym for Binary Large OBjects. Sometimes, due to security and portability concerns, you may feel the need to save binary data on your MySQL database alongside other related facts. For instance, if you are running a students' database, you may store their photos and scanned PDF documents together with their registration details such as names and addresses.

This is where BLOB comes in. A BLOB column stores actual binary strings or byte strings in a MySQL database instead of file path reference. This has one advantage; when you backup your database, your entire application data is copied over.

In this guide, we will show you how you can store binary data on your MySQL database either hosted with Alibaba Cloud ApsaraDB or provisioned on an ECS instance.

Prerequisites

A valid Alibaba Cloud account. Signup now and get up to $1200 worth of credit to test 40+ Alibaba Cloud products including ApsaraDB and ECS to run MySQL databases.
An Alibaba ECS instance running any Linux distribution.
Apache web server.
PHP scripting language.
A MySQL database server either hosted on an ECS instance or ApsaraDB.
A MySQL user account that has ability to create databases and tables.

Step 1: Login to your MySQL Database

$ mysql -uroot -p -h127.0.0.1

Remember to replace root with the appropriate username and use the correct IP address or hostname in place of 127.0.0.1.

Step 2: Choosing a BLOB Data Type

MySQL supports 4 types of BLOB data type which only differ in the maximum length of data they can store. Here is a summary of the different types:

TINYBLOB: Only supports up to 255 bytes.
BLOB: Can handle up to 65,535 bytes of data.
MEDIUMBLOB: The maximum length supported is 16,777,215 bytes.
LONGBLOB: Stores up to 4,294,967,295 bytes of data.

So before, we design the database, we should make sure the data type we choose can comfortably handle the data that we intend to save. Also remember you can not have a default value for a BLOB column.

For demonstration purposes, we will use the below Alibaba Cloud Logo in .jpg format. It has about 24 kb, so the BLOB data type will be sufficient to store the image file because it can support upto 65 kb (65,535 bytes) of data.

Step 3: Creating the Database Structure

Before we create our table, we must have a database named my_school. Run the command below to create the database:

mysql>Create database my_school CHARACTER SET utf8 COLLATE utf8_general_ci;

Then, run the use command to select my_school database:

mysql>use my_school;

Create the students table with 3 columns using the below SQL command:

mysql> create table students ( 
student_id BIGINT PRIMARY KEY,
student_name VARCHAR(50) NOT NULL, 
photo BLOB NOT NULL
) Engine = InnoDB;

Step 4: Inserting Data to the Students Table

We are going to use PHP scripting language to handle write and read operations in the MySQL database. We will upload the Alibaba Cloud image.jpg image file on our web server and take note of the file path.

In our case, we will upload the file on the /var/www/example.com/public_html directory.. The path of our image file will look like this at the end:

/var/www/example.com/public_html/image.jpg

Then, we are going to use the below PHP script named register.php to save the student's information alongside the photo in our database. Remember to replace localhost, root and PASSWORD with the appropriate values.

<?php

$con = mysqli_connect("localhost","root","PASSWORD","my_school");

if (mysqli_connect_errno())
{ 
echo mysqli_error($con);
exit();
}

$imgbinarydata = mysqli_real_escape_string($con, file_get_contents("/var/www/example.com/public_html/image.jpg" ));

$sql= "INSERT INTO students (student_id, student_name, photo) VALUES ('1','JOHN DOE','$imgbinarydata')";

if (!mysqli_query($con,$sql)){
echo mysqli_error($con);
}

else {

echo "Record inserted successfully";

}

mysqli_close($con);

?>

Upload the register.php file on the root of your website and run it once from a browser to execute the student registration process:

http://www.example.com/register.php

If there are no errors, you should get a success response.

Output:

You can actually confirm if the record was saved in the database by running a select query against the students table. However, the SQL command below uses the left function to retrieve only some part of binary data from the image column otherwise, the output may fill your screen.

mysql> select student_id, student_name, LEFT(photo , 30) as photo FROM students;
+------------+--------------+--------------------------------+
| student_id | student_name | photo                          |
+------------+--------------+--------------------------------+
|          1 | JOHN DOE     | ???? JFIF  ` `  ?? \Exif         |
+------------+--------------+--------------------------------+
1 row in set (0.00 sec)

Step 4: Retrieving Data from the Students Table

We have created our database and saved one student data together with an image. We can now go ahead and try to retrieve the information we saved and convert it back to a viewable image on our website.

We are going to use the below PHP script named view.php to do this. Upload the file on your website and load it to view the data and remember to replace localhost, root and PASSWORD with the appropriate values:

<?php

$con = mysqli_connect("localhost","root","PASSWORD","my_school");

if (mysqli_connect_errno()){ 
echo mysqli_error($con);
exit();
}

    
$sql = "select * from students" ;

if (!$result=mysqli_query($con, $sql)){
echo mysqli_error($con);
}

else {

$row = mysqli_fetch_array($result);

header('Content-type: image/jpeg'); 

echo $row["photo"];

}

mysqli_close($con);

?>

Then, run the view.php file on a browser window to retrieve the image:

http://www.example.com/view.php

Output:

As you can see above, we were able to retrieve our image back and this means we have successfully saved binary data in a MySQL Database hosted on Alibaba Cloud.

Benefits of storing Binary Data in MySQL Database

You can see that MySQL supports binary data storage very effectively. Remember, you can store any file type including PDF documents, MP3 Files and Video Files.

Here are some pros about storing binary data in a database:

The data is more secure because it is protected against theft from simple file copying of directories.
The data is almost free from physical file infection by viruses. As long as you protect the database, your data is isolated from the file system.
Retrieving data from the MYSQL database is fast and efficient.
Centralized repository for your application data. Utilizing BLOB allows you to have a central database for all your application data and this means less pain when dealing with portability and backup issues. This also leads to data integrity.

Conclusion

In this article, we have showed you how to use BLOB data type to store images in your MySQL database hosted on Alibaba Cloud ECS or ApsaraDB. Although the PHP scripts we have used in this guide are for demonstration purposes, you can extend them further to accommodate binary data in your application. If you are new to Alibaba Cloud, you can sign up to get free credit of up to $1200 and test MySQL databases and over 40 products on their platform.

Community

How to Work with BLOB in MySQL Database Hosted on Alibaba Cloud

Prerequisites

Step 1: Login to your MySQL Database

Step 2: Choosing a BLOB Data Type

Step 3: Creating the Database Structure

Step 4: Inserting Data to the Students Table

Step 4: Retrieving Data from the Students Table

Benefits of storing Binary Data in MySQL Database

Conclusion

Read previous post:

Read next post:

francisndungu

You may also like

Comments

francisndungu

Related Products

Database Security Solutions

PolarDB for MySQL

Security Solution

AnalyticDB for MySQL