What Is the True Maximum Length of MySQL VARCHAR? MySQL Storage Limits Explained

When creating MySQL tables we often need to define string columns.

CREATE TABLE `user` (
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Two types are suitable for this scenario: char and varchar . Both require a length specification, e.g., char(100) or varchar(100), where the number indicates the maximum number of characters.

The difference is that char reserves a fixed amount of space, while varchar stores only the actual length, saving space.

Many assume the maximum length of a varchar column is 65535, but experiments show that the real limit varies with character set, nullability, and the number of columns.

VARCHAR maximum length experiment

Creating a varchar(65535) column fails with an error indicating the maximum length is 16383 for utf8mb4.

Changing the length to 16383 succeeds.

Impact of character set

The character set determines how many bytes a single character may occupy (the maxlen value). You can view supported character sets with show charset;.

Results of the experiment:

utf8mb4 (maxlen=4) → maximum varchar length ≈ 16383 (4 × 16383 = 65532).

utf8mb3 (maxlen=3) → maximum varchar length ≈ 21844 (3 × 21844 = 65532).

latin1 (maxlen=1) → maximum varchar length ≈ 65533 (1 × 65533 = 65533).

Thus the length specified for a varchar column represents the maximum number of characters, while maxlen is the maximum bytes per character; their product approximates the 65535‑byte row limit.

Effect of NULLability

Columns that can be NULL require an extra byte to record nullability. Changing a column from NOT NULL to NULL reduces the maximum length by one byte.

Effect of column count

When a table contains multiple columns, the total size of all columns (excluding hidden columns and record header) must stay under 65535 bytes, so the maximum length of a single varchar column decreases.

Why not 65535 but 65533?

The row format stores a two‑byte length field for variable‑length columns, consuming two bytes of the 65535‑byte limit.

Row storage format

Using show table status you can see the row format, which is usually Dynamic .

Dynamic rows consist of two parts: extra information (variable‑length field list, NULL bitmap, record header) and the actual data.

Hidden columns such as Row_ID, trx_id, and roll_pointer also occupy space.

For a single NOT NULL varchar column, the extra information uses the two‑byte length field, leaving 65533 bytes for the actual data.

Handling strings larger than 64 KB

If the data exceeds the varchar limit, use TEXT or BLOB types, which also store data off‑page with a 20‑byte pointer.

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

Reading rows that contain off‑page data requires additional I/O.

Difference between BLOB and TEXT

BLOB has no character set, while TEXT does; BLOB can also store binary data such as images or videos.

Summary

Modern MySQL tables usually use the Dynamic row format, which consists of extra information and real data.

The maximum size of a row (excluding hidden columns) is 65535 bytes.

For a table with a single NOT NULL varchar column, the maximum length is roughly 65535 divided by the charset's maxlen.

Use LONGTEXT or LONGBLOB for fields larger than 64 KB.

Dynamic rows store a 20‑byte pointer for off‑page data, increasing disk I/O when such data is read.

BLOB lacks charset support but can store binary content.

References

《mysql技术内幕》

《从根儿理解mysql》