Maximum we can have 4096 columns per table, but exact limit depends on several factors, listed below:
utf8characters require up to three bytes per character, so for a
CHAR(255) CHARACTER SET utf8column, the server must allocate 255 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.
.frmfile 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)
.frmfile 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_lengthis space needed for screens? This is related to MySQL's Unireg heritage.
create_fields.elementsis the number of columns.
n_lengthis the total length of all column names, including one byte per name as a separator.
int_lengthis related to the list of values for
com_lengthis the total length of column and table comments.
SETcolumns, or use of column or table comments.
a) InnoDBpermits no more than 1000 columns.