BLOB and binary types in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does BLOB stand for in MySQL?

BLOB stands for Binary Large Object. It is used to store large amounts of binary data like images or files.

intermediate

Name the four BLOB types in MySQL and their maximum storage sizes.

The four BLOB types are:
1. TINYBLOB - up to 255 bytes
2. BLOB - up to 65,535 bytes (64 KB)
3. MEDIUMBLOB - up to 16,777,215 bytes (16 MB)
4. LONGBLOB - up to 4,294,967,295 bytes (4 GB)

intermediate

What is the difference between BINARY and VARBINARY types in MySQL?

BINARY stores fixed-length binary data, padding with zeros if needed.
VARBINARY stores variable-length binary data without padding.

beginner

When should you use BLOB types instead of TEXT types in MySQL?

Use BLOB types when you need to store binary data like images or files.
Use TEXT types for storing text data.

intermediate

How does MySQL treat BLOB data differently from character data?

MySQL treats BLOB data as raw bytes without character set or collation.
Character data is stored with encoding and collation rules.

Which MySQL data type is best for storing a small image file?

A TEXT

B BLOB

C VARCHAR

D INT

BLOB is designed to store binary data like images, unlike VARCHAR or TEXT which store text.

What is the maximum size of a MEDIUMBLOB in MySQL?

A 4 GB

B 64 KB

C 255 bytes

D 16 MB

MEDIUMBLOB can store up to 16,777,215 bytes, which is about 16 MB.

Which binary type stores fixed-length data padded with zeros if needed?

A VARBINARY

B TEXT

C BINARY

D BLOB

BINARY stores fixed-length binary data and pads with zeros if the data is shorter.

How does MySQL treat BLOB data compared to TEXT data?

A BLOB stores raw bytes, TEXT stores encoded characters

B BLOB is case-insensitive, TEXT is case-sensitive

C BLOB stores text, TEXT stores binary

D Both are treated the same

BLOB stores raw binary data without encoding; TEXT stores character data with encoding.

Which BLOB type can store the largest amount of data?

A LONGBLOB

B BLOB

C MEDIUMBLOB

D TINYBLOB

LONGBLOB can store up to 4 GB of binary data, the largest among BLOB types.

Explain the differences between BINARY, VARBINARY, and BLOB types in MySQL.

Think about length and size differences and what kind of data each stores.

Describe when and why you would choose a BLOB type over a TEXT type in a MySQL database.

Consider the type of data you want to store and how MySQL treats it.

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