BLOB and binary types in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this BLOB insertion query?

Consider a MySQL table files with a data column of type BLOB. What will be the result of this query?

INSERT INTO files (data) VALUES (0x48656C6C6F);

What is stored in the data column?

MySQL

CREATE TABLE files (id INT AUTO_INCREMENT PRIMARY KEY, data BLOB);
INSERT INTO files (data) VALUES (0x48656C6C6F);
SELECT HEX(data) FROM files WHERE id = 1;

A "48656C6C6F"

B "0x48656C6C6F"

C "Hello"

D "68656C6C6F"

💡 Hint

Remember that 0x48656C6C6F is a hexadecimal literal representing bytes.

The query inserts the bytes represented by the hex literal 0x48656C6C6F into the BLOB column. Selecting HEX(data) returns the hexadecimal string representation of those bytes, which is "48656C6C6F".

🧠 Conceptual

intermediate

Which MySQL data type is best for storing fixed-length binary data?

You want to store a fixed-length binary string of exactly 16 bytes in MySQL. Which data type should you choose?

A TEXT

B VARBINARY(16)

C BINARY(16)

D BLOB

💡 Hint

Fixed-length binary means the storage size is always the same.

BINARY(16) stores fixed-length binary data of 16 bytes. VARBINARY is variable length, BLOB is for large binary objects, and TEXT is for character data.

📝 Syntax

advanced

Which query correctly creates a table with a MEDIUMBLOB column?

You want to create a MySQL table named images with a column img_data that can store up to 16 MB of binary data. Which query is correct?

A CREATE TABLE images (id INT PRIMARY KEY, img_data BLOB(16MB));

B CREATE TABLE images (id INT PRIMARY KEY, img_data BINARY(16777216));

C CREATE TABLE images (id INT PRIMARY KEY, img_data LONGBLOB(16));

D CREATE TABLE images (id INT PRIMARY KEY, img_data MEDIUMBLOB);

💡 Hint

MEDIUMBLOB is a valid MySQL data type for medium-sized binary data.

Option D uses the correct syntax and data type MEDIUMBLOB. Option D is invalid because BLOB does not take size parameters. Option D uses BINARY with a very large size which is not allowed. Option D uses LONGBLOB with a size parameter which is invalid.

❓ optimization

advanced

How to optimize queries on a table with large BLOB columns?

You have a MySQL table with a large BLOB column storing images. You often query metadata columns but rarely need the BLOB data. What is the best way to optimize query performance?

A Use SELECT * to always fetch all columns including BLOB.

B Split the table into two: one with metadata and one with BLOB data, joining only when needed.

C Convert BLOB columns to TEXT to improve query speed.

D Create an index on the BLOB column to speed up queries.

💡 Hint

Fetching large BLOB data unnecessarily slows queries.

Splitting the table separates large binary data from frequently queried metadata, reducing data read and improving performance. Indexing BLOB columns is not supported and converting to TEXT is inappropriate for binary data.

🔧 Debug

expert

Why does this query fail when inserting binary data?

Given the table:
CREATE TABLE docs (id INT PRIMARY KEY, content BLOB);
Why does this query fail?
INSERT INTO docs (id, content) VALUES (1, '0x4D7953514C');

A Because '0x4D7953514C' is treated as a string, not binary data.

B Because BLOB columns cannot store hexadecimal values.

C Because the id column is missing AUTO_INCREMENT.

D Because the quotes around 0x4D7953514C are required for binary literals.

💡 Hint

Hexadecimal literals should not be quoted in MySQL.

In MySQL, 0x4D7953514C without quotes is a binary literal. Quoting it makes it a string literal, which does not convert automatically to binary, causing the insert to fail or store wrong data.

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