Every table (regardless of storage engine) has a maximum row
size of 65,535 bytes. Storage engines may place additional
constraints on this limit, reducing the effective maximum
row size.
The maximum row size constrains the number (and possibly
size) of columns because the total length of all columns
cannot exceed this size. For example,
utf8
characters require up to three bytes
per character, so for a
CHAR(255) CHARACTER
SET utf8
column, the server must allocate 255
× 3 = 765 bytes per value. Consequently, a table
cannot contain more than 65,535 / 765 = 85 such columns.
Storage for variable-length columns includes length bytes,
which are assessed against the row size. For example, a
VARCHAR(255)
CHARACTER SET utf8
column takes two bytes to store
the length of the value, so each value can take up to 767
bytes.
BLOB
and
TEXT
columns count from one
to four plus eight bytes each toward the row-size limit
because their contents are stored separately from the rest
of the row.
Declaring columns NULL
can reduce the
maximum number of columns permitted. For
MyISAM
tables,
NULL
columns require additional space in
the row to record whether their values are
NULL
. Each NULL
column
takes one bit extra, rounded up to the nearest byte. The
maximum row length in bytes can be calculated as follows:
row length = 1
+ (sum of column lengths
)
+ (number of NULL columns
+ delete_flag
+ 7)/8
+ (number of variable-length columns
)
delete_flag
is 1 for tables with
static row format. Static tables use a bit in the row record
for a flag that indicates whether the row has been deleted.
delete_flag
is 0 for dynamic
tables because the flag is stored in the dynamic row header.
For information about MyISAM
table formats, see Section 14.1.3, “MyISAM
Table Storage Formats”.
These calculations do not apply for
InnoDB
tables. Storage size is
the same for NULL
and NOT
NULL
columns.
The following 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:
mysql> CREATE TABLE t1
-> (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
-> ENGINE = MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)
The following statement to create table
t2
fails because the columns are
NULL
and
MyISAM
requires additional
space that causes the row size to exceed 65,535 bytes:
mysql> CREATE TABLE t2
-> (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
-> ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs
The following statement to create table
t3
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 t3
-> (c1 VARCHAR(65535) NOT NULL)
-> ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs
Reducing the column length to 65,533 or less permits the
statement to succeed.
Each table has an .frm
file that
contains the table definition. The server uses the following
expression to check some of the table information stored in
the file against an upper limit of 64KB:
if (info_length+(ulong) create_fields.elements*FCOMP+288+
n_length+int_length+com_length > 65535L || int_count > 255)
The portion of the information stored in the
.frm
file that is checked against the
expression cannot grow beyond the 64KB limit, so if the
table definition reaches this size, no more columns can be
added.
The relevant factors in the expression are:
info_length
is space needed for
“screens.” This is related to MySQL's
Unireg heritage.
create_fields.elements
is the number
of columns.
FCOMP
is 17.
n_length
is the total length of all
column names, including one byte per name as a
separator.
int_length
is related to the list of
values for ENUM
and
SET
columns.
com_length
is the total length of
column and table comments.
Thus, using long column names can reduce the maximum number
of columns, as can the inclusion of
ENUM
or
SET
columns, or use of column
or table comments.