How does this error occur? How does InnoDB handle overflow?
We know that a column of type
VARCHAR(M) can occupy up to 65535 bytes. If we use the ASCII character set, one character represents one byte. Let’s see if
mysql> CREATE TABLE varchar_size_demo(
As we see from the error message, MySQL has a limitation on the maximum storage space occupied by a single record. Except columns with the type
TEXT, the sum of all other columns (excluding hidden columns and header) cannot add up to more than 65535 bytes. Therefore MySQL suggests that we change the storage type to
In addition to the actual data, the 65535 bytes also includes other storage overhead. For example, in order to store a column of type
VARCHAR, InnoDB makes use of 3 storage spaces.
- actual data
- bytes occupied by the actual data
VARCHAR column does not have a
NOT NULL attribute, then it can store up to 65532 bytes. 2 bytes to store the length of
VARCHAR, and 1 byte to indicate if it is null.
VARCHAR column has a
NOT NULL attribute, then it is able to store 65533 bytes. It is not necessary to indicate whether it is null or not.
A page is typically 16KB in size, which is 16384 bytes. How does InnoDB handle
In situations where a column occupies a large storage space,
REDUNDANT manage to store only a part of the data in the current page, and dispersedly store the remaining data in other pages. To locate the pages, both formats use 20 bytes from the
actual data section (refer to this post) to store the addresses pointing to these locations.
They are similar to the
COMPACT format, except the way they deal with row overflow. They manage to store all the data in other pages, and only have the addresses pointing to these locations.