MySQL BLOB Types: Binary Data and Size Limits

MySQL BLOB types store binary strings (bytes), not text with a character set. The four types differ by their maximum value length:

These are type capacities, not guaranteed client/server transfer sizes. Available memory and communication buffers, including max_allowed_packet on both the server and client, can impose a lower limit. See MySQL’s BLOB and TEXT type reference.

Define a BLOB column

Use the smallest BLOB type that can hold the binary values the application accepts. This example stores a small thumbnail:

CREATE TABLE user_avatars (
    user_id INT PRIMARY KEY,
    thumbnail MEDIUMBLOB NOT NULL
);

The application should send the binary value using a parameterized statement or its database driver, rather than building SQL text from file contents. For larger objects, select MEDIUMBLOB or LONGBLOB according to the expected byte size and verify server and client packet limits.

BLOB and TEXT store different kinds of values

BLOB values are binary strings: comparisons and sorting use byte values. The parallel TEXT family stores nonbinary character strings and uses a character set and collation. Use MEDIUMTEXT or another TEXT type for textual content; its limit is also measured in bytes, while the number of characters depends on encoding.

If a BLOB or TEXT value exceeds its type limit, strict SQL mode rejects nonspace truncation. Without strict mode, MySQL can truncate the value and issue a warning. Large BLOB/TEXT columns can also cause queries using temporary tables to use disk, so select them only when needed rather than using SELECT * for large tables. See MySQL’s BLOB and TEXT handling notes.