MySQL represents each table by an
table format (definition) file in the database directory. The
storage engine for the table might create other files as well.
InnoDB table created in a
file-per-table tablespace or general tablespace, table data and
associated indexes are stored in a
.ibd file in the database
directory. When an
InnoDB table is created in
the system tablespace, table data and indexes are stored in the
ibdata* files that
represent the system tablespace. The
controls whether tables are created in file-per-table
tablespaces or the system tablespace, by default. The
TABLESPACE option can be used to place a
table in a file-per-table tablespace, general tablespace, or the
system tablespace, regardless of the
MyISAM tables, the storage engine creates
data and index files. Thus, for each
tbl_name, there are three disk
||Table format (definition) file|
Chapter 15, Alternative Storage Engines, describes what files each storage engine creates to represent tables. If a table name contains special characters, the names for the table files contain encoded versions of those characters as described in Section 9.2.4, “Mapping of Identifiers to File Names”.
As described previously, 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_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
SETcolumns. In this context, “int” does not mean “integer.” It means “interval,” a term that refers collectively to
com_lengthis the total length of column comments.
The expression just described has several implications for permitted table definitions:
Using long column names can reduce the maximum number of columns, as can the inclusion of
SETcolumns, or use of column comments.
A table can have no more than 255 unique
SETdefinitions. Columns with identical element lists are considered the same against this limt. For example, if a table contains these two columns, they count as one (not two) toward this limit because the definitions are identical:
e1 ENUM('a','b','c') e2 ENUM('a','b','c')
The sum of the length of element names in the unique
SETdefinitions counts toward the 64KB limit, so although the theoretical limit on number of elements in a given
ENUMcolumn is 65,535, the practical limit is less than 3000.