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.