C.10.4 Limits on Table Column Count and Row Size

Column Count Limits

MySQL has hard limit of 4096 columns per table, but the
effective maximum may be less for a given table. The exact
column limit depends on several factors:

The maximum row size for a table constrains the number
(and possibly size) of columns because the total length of
all columns cannot exceed this size. See
Row Size Limits.

The storage requirements of individual columns constrain
the number of columns that fit within a given maximum row
size. Storage requirements for some data types depend on
factors such as storage engine, storage format, and
character set. See Section 11.7, “Data Type Storage Requirements”.

Row Size Limits

The maximum row size for a given table is determined by
several factors:

The internal representation of a MySQL table has a maximum
row size limit of 65,535 bytes, even if the storage engine
is capable of supporting larger rows.
BLOB and
TEXT columns only
contribute 9 to 12 bytes toward the row size limit because
their contents are stored separately from the rest of the
row.

The maximum row size for an InnoDB
table, which applies to data stored locally within a
database page, is slightly less than half a page. For
example, the maximum row size is slightly less than 8KB
for the default 16KB InnoDB page size,
which is defined by the
innodb_page_size
configuration option. See
Section 14.6.1.6, “Limits on InnoDB Tables”.

If a row containing
variable-length
columns exceeds the InnoDB
maximum row size, InnoDB selects
variable-length columns for external off-page storage
until the row fits within the InnoDB
row size limit. The amount of data stored locally for
variable-length columns that are stored off-page differs
by row format. For more information, see
Section 14.11, “InnoDB Row Formats”.

Different storage formats use different amounts of page
header and trailer data, which affects the amount of
storage available for rows.

Row Size Limit Examples

The MySQL maximum row size limit of 65,535 bytes is
demonstrated in the following InnoDB
and MyISAM examples. The limit is
enforced regardless of storage engine, even though the
storage engine may be capable of supporting larger rows.

In the following MyISAM example,
changing a column to TEXT
avoids the 65,535-byte row size limit and permits the
operation to succeed because
BLOB and
TEXT columns only
contribute 9 to 12 bytes toward the row size.

Storage for variable-length columns includes length bytes,
which are counted toward the row size. For example, a
VARCHAR(255)
CHARACTER SET utf8mb3 column takes two bytes to
store the length of the value, so each value can take up
to 767 bytes.

The statement to create table t1
succeeds because the columns require 32,765 + 2 bytes and
32,766 + 2 bytes, which falls within the maximum row size
of 65,535 bytes:

The statement to create table t2 fails
because, although the column length is within the maximum
length of 65,535 bytes, two additional bytes are required
to record the length, which causes the row size to exceed
65,535 bytes:

mysql> CREATE TABLE t2
(c1 VARCHAR(65535) NOT NULL)
ENGINE = InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

Reducing the column length to 65,533 or less permits the
statement to succeed.

InnoDB restricts row size (for data
stored locally within the database page) to slightly less
than half a database page. For example, the maximum row
size is slightly less than 8KB for the default 16KB
InnoDB page size, which is defined by
the innodb_page_size
configuration option.

The statement to create table t4 fails
because the defined columns exceed the row size limit for
a 16KB InnoDB page.

Note

innodb_strict_mode is
enabled in the following example to ensure that
InnoDB returns an error if the
defined columns exceed the InnoDB row
size limit. When
innodb_strict_mode is
disabled (the default), creating a table that uses
REDUNDANT or
COMPACT row format succeeds with a
warning if the InnoDB row size limit
is exceeded.

DYNAMIC and
COMPRESSED row formats are more
restrictive in this regard. Creating a table that uses
DYNAMIC or
COMRESSED row format fails with an
error if the InnoDB row size limit is
exceeded, regardless of the
innodb_strict_mode
setting.