DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
The Latest "Software Integration: The Intersection of APIs, Microservices, and Cloud-Based Systems" Trend Report
Get the report
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Work With BLOB in a MySQL Database Hosted on Alibaba Cloud

How to Work With BLOB in a MySQL Database Hosted on Alibaba Cloud

See how to work with BLOB in a MySQL Database.

Francis Ndungu user avatar by
Francis Ndungu
·
Mar. 26, 19 · Tutorial
Like (3)
Save
Tweet
Share
83.06K Views

Join the DZone community and get the full member experience.

Join For Free

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 a file path reference. This has one advantage; when you back up 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.Image title

Prerequisites

  1. A valid Alibaba Cloud account. You can 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 the ability to create databases and tables.

Step 1: Log in to Your MySQL Database

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

$ 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 types, 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 cannot 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 up to 65 kb (65,535 bytes) of data.

Image title

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
http://www.example.com/register.php

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

Output:

Image title

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:

Image title

As you can see above, we were able to retrieve our image back, which 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 shown you how to use a 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.

Database MySQL Alibaba Cloud Cloud Data (computing)

Published at DZone with permission of Francis Ndungu, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • The Path From APIs to Containers
  • How To Handle Secrets in Docker
  • Fargate vs. Lambda: The Battle of the Future
  • REST vs. Messaging for Microservices

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: