SQL MEDIUMBLOB

What is MEDIUMBLOB in SQL?

A MySQL binary large object column type that stores up to 16,777,215 bytes (16 MB) of raw binary data.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL MEDIUMBLOB: MySQL, MariaDB

SQL MEDIUMBLOB Full Explanation

MEDIUMBLOB is one of MySQL’s four BLOB types (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB). It is designed for medium-sized binary payloads such as small images, compressed documents, or serialized objects that exceed 64 KB but are unlikely to surpass 16 MB. Internally MySQL prefixes each MEDIUMBLOB value with a 3-byte length field, allowing values from 0 to 16,777,215 bytes. MEDIUMBLOB data is treated as raw bytes—no character set or collation is applied—so functions that expect text will not work without conversion. The type occupies only as much storage as the data plus the 3-byte prefix. When used in an InnoDB table, MEDIUMBLOB values longer than the InnoDB page size (default 16 KB) are stored off-page, with the primary key pointing to the overflow pages. MEDIUMBLOB cannot have a default value prior to MySQL 8.0.13, cannot participate in FULLTEXT indexes, and obeys the same nullability and indexing rules as other BLOB types.

SQL MEDIUMBLOB Syntax

column_name MEDIUMBLOB

SQL MEDIUMBLOB Parameters

Example Queries Using SQL MEDIUMBLOB

-- Create a table with a MEDIUMBLOB column
CREATE TABLE product_images (
  id INT PRIMARY KEY AUTO_INCREMENT,
  image MEDIUMBLOB NOT NULL,
  mime_type VARCHAR(32) NOT NULL
);

-- Insert a PNG file using a parameterized query
INSERT INTO product_images (image, mime_type)
VALUES (LOAD_FILE('/var/data/logo.png'), 'image/png');

-- Retrieve the binary data
SELECT id, OCTET_LENGTH(image) AS bytes
FROM product_images
WHERE id = 1;

Expected Output Using SQL MEDIUMBLOB

The first query creates the table
The INSERT stores the contents of logo
png as a binary blob
The SELECT returns the row with the byte length of the stored file (e
g
, 152734 bytes)

Use Cases with SQL MEDIUMBLOB

Storing user-uploaded images or avatars smaller than 16 MB
Saving compressed JSON or XML documents
Persisting serialized objects from application code
Holding audio snippets, small videos, or PDFs that do not exceed 16 MB
Caching medium-sized binary assets when a file system is not available

Common Mistakes with SQL MEDIUMBLOB

Assuming MEDIUMBLOB is affected by the connection’s character set—it is always raw bytes.
Selecting MEDIUMBLOB data without using functions like HEX, BASE64_ENCODE, or casting in clients that cannot display binary.
Forgetting the 16 MB limit and receiving "Data too long for column" errors.
Creating indexes on large MEDIUMBLOB columns without a length prefix (e.g., INDEX (image(255))).
Expecting MEDIUMBLOB to store default values in MySQL versions older than 8.0.13.

First Introduced In

MySQL 3.23

Frequently Asked Questions

How do I read MEDIUMBLOB data in a client?

Most MySQL clients cannot display raw binary. Wrap the column with HEX(), BASE64_ENCODE(), or export it to a file.

Can MEDIUMBLOB columns be indexed?

Yes, but only on a prefix length. For example: `CREATE INDEX idx_img ON product_images (image(255));` Large prefixes increase index size.

Is MEDIUMBLOB affected by character set settings?

No. MEDIUMBLOB is binary. Character sets and collations are ignored.

Can I set a default value for a MEDIUMBLOB column?

From MySQL 8.0.13 onward you can assign a literal or expression default. Earlier versions disallow defaults on BLOB types.