BLOB and binary types in MySQL - Step-by-Step Execution

Concept Flow - BLOB and binary types

Start: Define column with BLOB or binary type

↓

Insert binary data into column

↓

Store data as raw bytes

↓

Retrieve data as binary

↓

Use or convert data as needed

↓

End

The flow shows how binary data is stored and retrieved using BLOB or binary types in MySQL.

Execution Sample

MySQL

CREATE TABLE images (
  id INT PRIMARY KEY,
  data BLOB
);

INSERT INTO images VALUES (1, LOAD_FILE('cat.jpg'));

SELECT id, LENGTH(data) FROM images;

This code creates a table with a BLOB column, inserts an image file as binary data, and retrieves the size of the stored data.

Execution Table

Step	Action	Evaluation	Result
1	Create table with BLOB column	Table 'images' created	Table ready to store binary data
2	Insert binary data from file 'cat.jpg'	LOAD_FILE reads file bytes	Row inserted with binary data in 'data' column
3	Select id and LENGTH(data)	Calculate length of binary data	Returns id=1 and size of binary data in bytes
4	End	No more actions	Execution complete

💡 All steps executed; binary data stored and retrieved successfully

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
images table	empty	1 row with id=1 and binary data	1 row with id=1 and binary data	1 row with id=1 and binary data
data (BLOB)	NULL	binary bytes of 'cat.jpg'	binary bytes of 'cat.jpg'	binary bytes of 'cat.jpg'
LENGTH(data)	NULL	NULL	size in bytes (e.g. 204800)	size in bytes (e.g. 204800)

Key Moments - 2 Insights

Why do we use BLOB instead of VARCHAR for storing images?

BLOB stores raw binary data without character encoding, unlike VARCHAR which stores text. See execution_table step 2 where binary bytes are inserted.

What does LENGTH(data) return for a BLOB column?

It returns the number of bytes stored, not characters. Refer to execution_table step 3 where LENGTH(data) gives the binary size.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of step 3?

A Returns the id and size of binary data in bytes

B Returns the text content of the image

C Returns NULL because data is binary

D Returns the file path of the image

💡 Hint

Check the 'Result' column in execution_table row for step 3

At which step is the binary data actually stored in the table?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column in execution_table to find when data is inserted

If we change the column type from BLOB to VARCHAR, what would happen to the stored data?

A Data size would increase but remain binary

B Binary data would be stored correctly as before

C Binary data would be stored as text, possibly corrupted

D MySQL would reject the insert

💡 Hint

Refer to key_moments about difference between BLOB and VARCHAR storage

Concept Snapshot

BLOB and binary types store raw binary data like images or files.
Use BLOB for variable-length binary data.
Use BINARY/VARBINARY for fixed/variable-length binary data.
Data is stored as bytes, not text.
Retrieve with functions like LENGTH() to get byte size.
Ideal for storing files inside the database.

Full Transcript

This visual execution shows how MySQL handles BLOB and binary types. First, a table with a BLOB column is created. Then, binary data from a file is inserted into the table. The data is stored as raw bytes, not text. When retrieving, functions like LENGTH() return the size in bytes. This process is important because BLOB stores binary data safely, unlike text types which can corrupt binary content. The execution table traces each step from table creation, data insertion, to data retrieval, showing variable states and results. Key moments clarify why BLOB is used and how LENGTH() works on binary data. The quiz tests understanding of these steps and concepts.

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