What are the limits on the number of columns and row sizes in a MySQL table?

The number of columns to limit

MySQL has a hard limit of 4096 columns per table, but for a given table the effective maximum may be less. The exact column limit depends on several factors:

  • The maximum row size of a table limits the number (and possibly size) of columns because the total length of all columns cannot exceed that size

  • The storage requirements for 1 column limit the number of columns within a given maximum row size. The storage requirements for certain data types depend on factors such as the storage engine, storage format, and character set

  • Storage engines may impose additional restrictions on table column counts. For example, InnoDB has a limit of 1017 columns per table

  • The function key portion is implemented as a hidden, virtually generated storage column, so each function key portion in the table index counts toward the total column limit for the table.

Row size limit

The maximum row size of a given table is determined by several factors:

  • The internal representation of MySQL tables has a maximum row size limit of 65,535 bytes, even if the storage engine can support larger rows. The BLOB and TEXT columns are only helpful towards the 9 – to 12-byte limit of the row size, because their content is stored separately from the rest of the row.

  • InnoDB sets 4KB, 8KB, 16KB, and 32KBinnodb_page_size, and the maximum row size of the table (for data stored locally in the database page) is slightly less than half the page size. For example, for the default 16KBInnoDB page size, the maximum row size is a little less than 8KB. For 64KB pages, the maximum row size is slightly less than 16KB

  • If InnoDB rows containing variable-length columns exceed the maximum row size, InnoDB selects variable-length columns for external out-of-page storage until the row fits the InnoDB row size limit. For variable-length columns stored outside the row, the amount of data stored locally varies depending on the row format.

  • Different storage formats use different amounts of page header and tail data, which can affect the amount of available storage for rows.

Row size limit case

In the MyISAM example below, changing the column TEXT avoids the row size limit of 65535 bytes and allows success because manipulating the BLOB and TEXT columns only helps toward the row size of 9 to 12 bytes.

The InnoDB table was successfully executed because the column was changed to TEXT to avoid MySQL’s 65,535 byte row size limit, and InnoDB’s variable-length column off-page storage avoided InnoDB’s row size limit.

The storage for variable-length columns includes length bytes, which are counted toward the row size. For example, the VARCHAR(255) CHARACTER SET UTF8MB3 column requires two bytes to store the length of the value, so each value can take up to 767 bytes.

T1 The statement created the table successfully with a maximum row size of 65,535 bytes because the columns require 32,765 + 2 bytes and 32,766 + 2 bytes:

Reducing the column length to 65,533 or less allows the statement to succeed.

For MyISAM tables, NULL columns require extra space in the row to record their value NULL. Each NULL column requires an extra bit of value rounded up to the nearest byte.

Create table statement T3 failed because in addition to the space required for variable length column length bytes, MyISAM also required space for NULL columns, resulting in row sizes exceeding 65,535 bytes:

InnoDB limits the row size (for data stored locally in database pages) to slightly less than 4KB, 8KB, 16KB, and 32KB. Innodb_page_size limits the row size to slightly less than 16KB for 64KB pages.

T4 failed to create a table because the columns defined exceeded the 16KB InnoDB page row size limit.