MySQL TEXT and BLOB: Sizes, Limits and Better Choices

When text is too long for VARCHAR, use a TEXT type. For raw bytes (images, documents) the equivalent is BLOB.

Text type	Maximum size	Binary type
`TINYTEXT`	255 bytes	`TINYBLOB`
`TEXT`	65,535 bytes (64 KB)	`BLOB`
`MEDIUMTEXT`	16,777,215 bytes (16 MB)	`MEDIUMBLOB`
`LONGTEXT`	4,294,967,295 bytes (4 GB)	`LONGBLOB`

The size is in bytes, so a TEXT column holds fewer than 65,535 characters of Hindi or emoji.

query.sql

SQL

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    body MEDIUMTEXT NOT NULL
);
INSERT INTO articles (title, body) VALUES ('Hello', REPEAT('Lorem ipsum ', 1000));
SELECT id, title, CHAR_LENGTH(body) AS body_chars, LENGTH(body) AS body_bytes FROM articles;

Output

OUTPUT

+----+-------+------------+------------+
| id | title | body_chars | body_bytes |
+----+-------+------------+------------+
|  1 | Hello |      12000 |      12000 |
+----+-------+------------+------------+
1 row in set

Differences from VARCHAR

No default value (before MySQL 8.0.13, never; since then only as an expression).
Indexes need a prefix length. You cannot index a whole TEXT column. You index its first characters, or use a FULLTEXT index for searching (Full-Text Search).
Large values may be stored off-page, away from the rest of the row. Reading them costs an extra lookup.
Temporary tables that include TEXT columns often go to disk.

query.sql

SQL

1
2

CREATE TABLE notes (id INT, body TEXT);
CREATE INDEX idx_body ON notes (body);

Output

OUTPUT

ERROR 1170 (42000): BLOB/TEXT column 'body' used in key specification without a key length

query.sql

SQL

1
2

CREATE INDEX idx_body_prefix ON notes (body(50));
SHOW INDEX FROM notes;

Output

OUTPUT

+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| notes |          1 | idx_body_prefix |            1 | body        | A         |           0 |       50 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set

Habits that avoid trouble

**Do not SELECT * from a table with big text columns** when you only need the title. List the columns you need. Otherwise MySQL fetches and sends every article body.
Put huge text in a separate table if it is rarely read together with the other columns, for example article_bodies (article_id, body).
Use the smallest type that fits: TEXT for comments, MEDIUMTEXT for article bodies, LONGTEXT only when you really need more than 16 MB.

BLOB: should you store files in MySQL?

You can store images and PDFs in BLOB columns. It is rarely a good idea:

They make the database, its backups and replicas large and slow.
They bypass the web server’s fast static file handling and the CDN.
Image resizing and caching become your problem.

The usual design is to save the file on disk or in object storage (S3 and similar) and keep only its path or key, the size and the type in MySQL. Store a BLOB only for small, private binary values that must live and be backed up with the row, such as a small encrypted token or a thumbnail.

query.sql

SQL

CREATE TABLE uploads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    original_name VARCHAR(255) NOT NULL,
    storage_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size_bytes INT UNSIGNED NOT NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

lightbulb

A good default

Short text: VARCHAR. Long text: TEXT or MEDIUMTEXT. Files: on disk or in object storage, with a path in the table.