Maximum we can have 4096 columns per table, but exact limit depends on several factors, listed below:
utf8
characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8
column, the server must allocate 255 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns..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)
.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.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.ENUM
or SET
columns, or use of column or table comments.
a) InnoDB
permits no more than 1000 columns.