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

  1. 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.
  2. An Alibaba ECS instance running any Linux distribution.
  3. Apache web server.
  4. PHP scripting language.
  5. A MySQL database server either hosted on an ECS instance or ApsaraDB.
  6. A MySQL user account that has ability to create databases and tables.

Step 1: Login to your MySQL Database

Log in to your MySQL server on a command line interface. The basic syntax is shown below:

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:

  1. TINYBLOB: Only supports up to 255 bytes.
  2. BLOB: Can handle up to 65,535 bytes of data.
  3. MEDIUMBLOB: The maximum length supported is 16,777,215 bytes.
  4. 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.

Image for post
Image for post

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:

Then, run the use command to select my_school database:

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

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:

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.

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

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

Output:

Image for post
Image for post

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.

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:

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

Output:

Image for post
Image for post

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:

  1. The data is more secure because it is protected against theft from simple file copying of directories.
  2. 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.
  3. Retrieving data from the MYSQL database is fast and efficient.
  4. 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.

Reference:https://www.alibabacloud.com/blog/how-to-work-with-blob-in-mysql-database-hosted-on-alibaba-cloud_594270?spm=a2c41.12420469.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store