BLOB and binary types in MySQL - Mini Project: Build & Apply

Storing and Retrieving Images Using BLOB in MySQL

📖 Scenario: You are building a simple image gallery database where users can upload and store small images directly in the database. You will create a table to hold image data using BLOB type, configure a size limit, insert sample images as binary data, and then retrieve them.

🎯 Goal: Create a MySQL table with a BLOB column to store image data, set a maximum size limit, insert sample binary image data, and write a query to retrieve the images.

📋 What You'll Learn

Create a table named gallery with columns id (integer primary key) and image_data of type BLOB

Add a size limit to the image_data column using MEDIUMBLOB type

Insert two rows with id values 1 and 2 and sample binary data for image_data

Write a SELECT query to retrieve all columns from the gallery table

💡 Why This Matters

🌍 Real World

Many applications store images, audio, or other binary files directly in databases using BLOB types for easy management and backup.

💼 Career

Understanding how to handle binary data in databases is important for backend developers, database administrators, and anyone working with multimedia data storage.

1

Create the gallery table with BLOB column

Write a SQL statement to create a table called gallery with two columns: id as an integer primary key, and image_data as a BLOB type.

MySQL

-- Your code here to create the gallery table with id and image_data columns

Need a hint?

Use CREATE TABLE with id INT PRIMARY KEY and image_data BLOB columns.

2

Change image_data column to MEDIUMBLOB for larger size

Modify the image_data column type to MEDIUMBLOB to allow storing larger images. Write an ALTER TABLE statement to change the column type.

MySQL

CREATE TABLE gallery (
  id INT PRIMARY KEY,
  image_data BLOB
);
-- Your code here to alter image_data to MEDIUMBLOB

Need a hint?

Use ALTER TABLE gallery MODIFY image_data MEDIUMBLOB; to change the column type.

3

Insert sample binary data into gallery table

Insert two rows into the gallery table with id values 1 and 2. Use the binary literals X'FFD8FFE0' and X'89504E47' as sample image_data values respectively.

MySQL

CREATE TABLE gallery (
  id INT PRIMARY KEY,
  image_data BLOB
);
ALTER TABLE gallery MODIFY image_data MEDIUMBLOB;
-- Your code here to insert two rows with binary data

Need a hint?

Use INSERT INTO gallery (id, image_data) VALUES (1, X'FFD8FFE0'); and similarly for the second row.

4

Write a SELECT query to retrieve all images

Write a SQL query to select all columns from the gallery table to retrieve the stored images.

MySQL

CREATE TABLE gallery (
  id INT PRIMARY KEY,
  image_data BLOB
);
ALTER TABLE gallery MODIFY image_data MEDIUMBLOB;
INSERT INTO gallery (id, image_data) VALUES (1, X'FFD8FFE0');
INSERT INTO gallery (id, image_data) VALUES (2, X'89504E47');
-- Your code here to select all rows from gallery

Need a hint?

Use SELECT * FROM gallery; to retrieve all rows.