The Maximum Number of Columns Per Table
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 (regardless of storage engine) has a maximum row size of 65,535 bytes. BLOB and TEXT columns are counted at one to four plus eight bytes per column toward the row size limit because the contents of these columns are stored separately from the rest of the row. Storage engines may place additional constraints on this limit, reducing the 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.
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
Bookmarks