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

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

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

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

Hint

Use SELECT * FROM gallery; to retrieve all rows.

Practice

1. Which MySQL data type is best suited for storing large binary files like images or videos?

easy

A. BLOB

B. VARCHAR

C. TEXT

D. INT

Solution

1. Step 1: Understand data types for binary storage

   BLOB is designed to store large binary data such as images or videos.

2. Step 2: Compare with other types

   VARCHAR and TEXT store text data, INT stores numbers, so they are not suitable for binary files.

3. Final Answer:

   BLOB -> Option A

4. Quick Check:

   Large binary data = BLOB [OK]

Hint: Large binary data? Choose BLOB type [OK]

Common Mistakes:

• Choosing VARCHAR or TEXT for binary data
• Using INT for storing files
• Confusing BLOB with text types

2. Which of the following is the correct way to declare a fixed-length binary column of 16 bytes in MySQL?

easy

A. BLOB(16)

B. VARBINARY(16)

C. BINARY(16)

D. TEXT(16)

Solution

1. Step 1: Identify fixed-length binary type

   BINARY(n) stores fixed-length binary data of n bytes.

2. Step 2: Check other options

   VARBINARY is variable-length, BLOB and TEXT are for large data, not fixed-length binary.

3. Final Answer:

   BINARY(16) -> Option C

4. Quick Check:

   Fixed-length binary = BINARY(n) [OK]

Hint: Fixed-length binary? Use BINARY(size) [OK]

Common Mistakes:

• Using VARBINARY for fixed-length data
• Trying to specify size with BLOB or TEXT
• Confusing TEXT with binary types

3. Given the table files(id INT, data BLOB), what will the following query return?

SELECT LENGTH(data) FROM files WHERE id = 1;

medium

A. The first byte of the 'data' column

B. The size in bytes of the binary data stored in 'data' for id 1

C. The number of characters in the 'data' column as text

D. An error because LENGTH() does not work on BLOB

Solution

1. Step 1: Understand LENGTH() on BLOB

   LENGTH() returns the number of bytes in the binary data stored in a BLOB column.

2. Step 2: Analyze the query

   The query selects LENGTH(data) for the row where id = 1, so it returns the size in bytes of that binary data.

3. Final Answer:

   The size in bytes of the binary data stored in 'data' for id 1 -> Option B

4. Quick Check:

   LENGTH(BLOB) = byte size [OK]

Hint: LENGTH() on BLOB returns byte size [OK]

Common Mistakes:

• Thinking LENGTH() counts characters in binary
• Expecting an error from LENGTH() on BLOB
• Confusing LENGTH() with SUBSTRING()

4. You wrote this table definition:

CREATE TABLE images (id INT, img BINARY(255));

But when inserting images larger than 100 bytes, data gets truncated. What is the problem?

medium

A. BINARY cannot store binary data, use TEXT instead

B. The id column should be AUTO_INCREMENT

C. You must use VARCHAR for binary data

D. BINARY(255) is fixed-length and pads or truncates data to 255 bytes

Solution

1. Step 1: Understand BINARY(n) behavior

   BINARY(n) stores fixed-length binary data of exactly n bytes, padding or truncating as needed.

2. Step 2: Identify truncation cause

   If inserted data is larger than 255 bytes, it will be truncated to fit fixed length.

3. Final Answer:

   BINARY(255) is fixed-length and pads or truncates data to 255 bytes -> Option D

4. Quick Check:

   Fixed-length truncates data > size [OK]

Hint: Fixed-length binary truncates data exceeding size [OK]

Common Mistakes:

• Thinking BINARY can store variable-length data
• Using VARCHAR for binary data
• Ignoring fixed-length padding/truncation

5. You want to store user profile pictures in a MySQL table with columns id INT and picture. You expect pictures up to 2MB. Which data type should you choose for picture and why?

hard

A. MEDIUMBLOB, because it supports up to 16MB of binary data

B. BINARY(2000000), because fixed-length is faster

C. VARCHAR(2000000), because it stores variable-length data

D. TEXT, because pictures are text data

Solution

1. Step 1: Check size requirements

   Pictures up to 2MB require a BLOB type that can hold at least 2 million bytes.

2. Step 2: Choose suitable BLOB type

   MEDIUMBLOB supports up to 16MB, enough for 2MB pictures. BINARY(2000000) is fixed-length and too large, VARCHAR and TEXT are for text, not binary.

3. Final Answer:

   MEDIUMBLOB, because it supports up to 16MB of binary data -> Option A

4. Quick Check:

   Large binary data > 64KB = MEDIUMBLOB [OK]

Hint: Use MEDIUMBLOB for binary data > 64KB [OK]

Common Mistakes:

• Using fixed-length BINARY for large data
• Choosing VARCHAR or TEXT for binary files
• Underestimating BLOB size limits