What’s the Real Maximum Length of MySQL VARCHAR? Myths, Charset Impact, and Row Format Explained

String Column Types in MySQL

MySQL provides CHAR and VARCHAR for storing textual data. Both require a length specification, e.g. char(100) or varchar(100), where the number denotes the maximum number of characters that can be stored.

Maximum VARCHAR Length Experiment

Attempting to create a column varchar(65535) fails with an error indicating the maximum allowed length is 16383 . Reducing the length to 16383 succeeds. Repeating the test with different character sets yields the following limits:

utf8mb4 (maxlen = 4) → max varchar length = 16383 characters (4 × 16383 = 65532 bytes)

utf8mb3 (maxlen = 3) → max varchar length = 21844 characters (3 × 21844 = 65532 bytes)

latin1 (maxlen = 1) → max varchar length = 65533 characters (1 × 65533 = 65533 bytes)

The length shown next to VARCHAR is the maximum number of characters; the maxlen of the character set is the maximum bytes per character. Their product approximates the 65535‑byte row limit.

Effect of NULLability

When a column is declared NOT NULL, MySQL does not need an extra byte for the NULL flag. Declaring the column as NULL reduces the usable length by one byte (e.g., latin1 drops from 65533 to 65532 characters).

Impact of Additional Columns

The 65535‑byte limit applies to the sum of all column data in a row (excluding hidden columns and the record header). Adding other columns such as INT (4 bytes) or BIGINT (8 bytes) reduces the space available for a VARCHAR column.

InnoDB Dynamic Row Format

Modern InnoDB tables use the Dynamic row format, which splits a row into:

Extra information : variable‑length field length list, NULL bitmap, and a 5‑byte record header.

Real data : actual column values plus hidden columns ( row_id, trx_id, roll_pointer) used for MVCC.

For a single VARCHAR column that is NOT NULL, the extra information occupies only a few bytes, leaving most of the 65535‑byte budget for the column data.

Row Overflow for Large VARCHAR Values

If a VARCHAR value exceeds the 16 KB page size, InnoDB stores a 20‑byte pointer in the row and places the actual data in an off‑page overflow area. This adds an extra I/O cost when reading such rows.

Storing Data Larger Than 64 KB

When the required size exceeds the VARCHAR limit, use TEXT or BLOB types. These types also store a 20‑byte pointer in the row and keep the bulk of the data off‑page. They come in TINY, MEDIUM, and LONG variants (up to ~4 GB). Example:

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

Differences Between BLOB and TEXT

TEXT respects the table’s character set and collation, allowing proper sorting and comparison. BLOB has no charset, making it suitable for binary data (images, videos) but unsuitable for text operations that rely on collation.

Summary of Key Points

MySQL rows are limited to 65535 bytes of user data (excluding hidden columns and the 5‑byte header).

The effective maximum length of a single NOT NULL VARCHAR column is roughly 65535 / charset.maxlen.

Allowing NULL reduces the maximum by one byte; each additional column further reduces the available space.

Values larger than 64 KB are stored off‑page with a 20‑byte pointer, incurring extra I/O.

Use TEXT / BLOB (or their LONG variants) for data exceeding the VARCHAR limit; choose TEXT when charset‑aware operations are needed.