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.