Maximum we can have 4096 columns per table, but exact limit depends on several factors, listed below:
1) 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,Â
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.
2) 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Â
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Â
SETcolumns, or use of column or table comments.
3) Individual storage engines might impose additional restrictions that limit table column count. Examples:
For more information: MySql