What’s the Real Maximum Length of MySQL VARCHAR? Myths, Charsets, and Storage Limits

Background and Common Misconception

When defining string columns in MySQL, developers usually choose CHAR or VARCHAR . A widespread belief is that VARCHAR can hold up to 65535 characters. An experiment creating a table with VARCHAR(65535) immediately fails, reporting a maximum length of 16383.

Impact of Character Set (charset)

The actual limit varies with the character set because MySQL stores characters as bytes. The MAXLEN value of a charset indicates the maximum bytes per character.

utf8mb4: MAXLEN = 4 → VARCHAR max length = 16383 (4 × 16383 = 65532 bytes)

utf8mb3: MAXLEN = 3 → max length = 21844 (3 × 21844 = 65532 bytes)

latin1: MAXLEN = 1 → max length = 65533 (1 × 65533 = 65533 bytes)

Thus the length shown in the column definition represents the maximum number of characters, while the underlying byte limit is always close to 65535.

Other Influencing Factors

NULLability : A nullable column consumes an extra byte to record the NULL flag, reducing the usable length by one byte.

Number of columns : The 65535‑byte row limit is shared by all columns. Adding more columns (or hidden columns such as ROW_ID, trx_id, etc.) shrinks the space available for a single VARCHAR field.

Row format : Modern MySQL tables use the Dynamic row format, which splits a row into extra information (variable‑length field list, NULL list, record header) and the actual data.

Why Not Exactly 65535?

The row header reserves two bytes to store the length of each variable‑length field. Consequently, a VARCHAR column that appears to allow 65533 characters actually uses those two bytes for its length metadata, leaving 65535 - 65533 = 2 bytes for that purpose.

Row Overflow and Off‑Page Storage

If a VARCHAR value exceeds the 16 KB page size, MySQL stores a 20‑byte pointer in the row and places the actual data on separate overflow pages. This mechanism is called row overflow and incurs additional disk I/O when reading the row.

Storing Larger Text

When data exceeds the practical VARCHAR limit (≈64 KB), the appropriate types are TEXT or BLOB, each offering TINY, MEDIUM, and LONG variants up to several gigabytes.

CREATE TABLE `test_max_length` (
  `test` LONGTEXT NOT NULL COMMENT '测试长度字段'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Difference between BLOB and TEXT : TEXT respects the table’s character set and collation, allowing proper sorting and comparison, while BLOB stores raw binary data without charset awareness. BLOB can also hold binary files such as images or videos, though object storage is usually preferred for large media.

Summary

MySQL tables typically use the Dynamic row format, separating extra row metadata from actual column data.

The maximum combined size of all columns in a row (excluding hidden columns and the record header) is 65535 bytes.

For a single NOT NULL VARCHAR column, the practical maximum length is roughly 65535 / MAXLEN of the chosen charset.

When a column is nullable, one byte is lost for the NULL flag; when multiple columns exist, each consumes part of the 65535‑byte budget.

Values larger than a 16 KB page trigger row overflow, storing a 20‑byte pointer in the row and the actual data on off‑page pages, which increases I/O cost.

For data exceeding ~64 KB, use TEXT / LONGTEXT or BLOB / LONGBLOB types. BLOB lacks charset support, while TEXT respects charset and collation, making TEXT suitable for textual data and BLOB for binary data.

References

《MySQL技术内幕》

《从根儿理解MySQL》