There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors, listed in the following discussion.
Every table has a maximum row size of 65,535 bytes. This maximum applies to all storage engines, but a given engine might have additional constraints that result in a lower effective maximum row size.
The maximum row size constrains the number of columns
because the total width of all columns cannot exceed this
size. For example,
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.
NULL can reduce the
maximum number of columns allowed.
columns require additional space in the row to record
whether their values are
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.
These calculations do not apply for
InnoDB tables, for which storage size is
no different for
NULL columns than for
NOT NULL columns.
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
The relevant factors in the expression are:
info_length is space needed for
“screens.” This is related to MySQL's
create_fields.elements is the number
FCOMP is 17.
n_length is the total length of all
column names, including one byte per name as a
com_length is the total length of
column and table comments.
Individual storage engines might impose additional restrictions that limit table column count. Examples: