Why InnoDB Limits VARCHAR to 16383 Characters and How It Stores Data

What Is InnoDB?

InnoDB is a MySQL storage engine that stores table data on disk.

How InnoDB Reads and Writes Data

Data is read from and written to disk in 16 KB pages, which are the basic unit of interaction between memory and storage. When a query accesses data for the first time, a 16 KB page is loaded into memory; subsequent accesses can use the cached page, avoiding further disk I/O.

Note: pagination queries and the 16 KB page concept are different.

innodb_page_size cannot be changed after the MySQL data directory is initialized.

InnoDB Row Formats

MySQL supports four row formats: Dynamic, Compact, Redundant, and Compressed. Since MySQL 5+, the default is Dynamic.

SHOW VARIABLES LIKE "innodb_default_row_format"

Variable‑Length Field Length List

For variable‑length columns (e.g., VARCHAR, TEXT, BLOB), InnoDB stores the actual byte length (L) of the value. It uses up to two bytes (an unsigned short) to record L, allowing a maximum of 2^16‑1 = 65535 bytes.

Because utf8mb4 can use up to 4 bytes per character, the maximum number of characters that can be recorded is 65535 / 4 = 16383.

CREATE TABLE test (c1 VARCHAR(10), c2 VARCHAR(10) NOT NULL, c3 CHAR(10), c4 VARCHAR(10)) CHARSET=utf8mb4;

INSERT INTO test (c1, c2, c3, c4) VALUES ('aaaa','你好啊','cc','d'),('eeee','fff',NULL,NULL);

How InnoDB Determines VARCHAR Length

Given a character set with maximum byte width W (utf8mb4: W=4), a VARCHAR(M) can store up to M × W bytes. The actual stored byte length L is recorded using 1 byte if L ≤ 127, otherwise 2 bytes.

SELECT LENGTH(c2) FROM test WHERE c1='aaaa';

NULL Value List

Columns that allow NULL are tracked in a separate NULL‑value list. Each such column gets one bit; bits are stored in byte order with high‑order bits padded with zeros if needed.

Overflow Columns

If a column's data exceeds the space available in the row, it becomes an overflow column. Only a 20‑byte pointer is stored in the row, while the actual data is kept in separate overflow pages linked as a singly‑linked list.

Further Reading

For more details on other row formats and record headers, see the book “How MySQL Works”. This article focuses on the default Dynamic format with the utf8mb4 character set, which is most relevant to everyday development.