Rechercher dans le manuel MySQL
C.10.4 Limits on Table Column Count and Row Size
Column Count Limits
MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors:
The maximum row size for a table constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. See Row Size Limits.
The storage requirements of individual columns constrain the number of columns that fit within a given maximum row size. Storage requirements for some data types depend on factors such as storage engine, storage format, and character set. See Section 11.8, “Data Type Storage Requirements”.
Storage engines may impose additional restrictions that limit table column count. For example,
InnoDB
has a limit of 1017 columns per table. See Section 15.6.1.6, “Limits on InnoDB Tables”. For information about other storage engines, see Chapter 16, Alternative Storage Engines.Functional key parts (see Section 13.1.15, “CREATE INDEX Syntax”) are implemented as hidden virtual generated stored columns, so each functional key part in a table index counts against the table total column limit.
The maximum row size for a given table is determined by several factors:
The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows.
BLOB
andTEXT
columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.The maximum row size for an
InnoDB
table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KBinnodb_page_size
settings. For example, the maximum row size is slightly less than 8KB for the default 16KBInnoDB
page size. For 64KB pages, the maximum row size is slightly less than 16KB. See Section 15.6.1.6, “Limits on InnoDB Tables”.If a row containing variable-length columns exceeds the
InnoDB
maximum row size,InnoDB
selects variable-length columns for external off-page storage until the row fits within theInnoDB
row size limit. The amount of data stored locally for variable-length columns that are stored off-page differs by row format. For more information, see Section 15.10, “InnoDB Row Formats”.Different storage formats use different amounts of page header and trailer data, which affects the amount of storage available for rows.
For information about
InnoDB
row formats, see Section 15.10, “InnoDB Row Formats”.For information about
MyISAM
storage formats, see Section 16.2.3, “MyISAM Table Storage Formats”.
Row Size Limit Examples
The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following
InnoDB
andMyISAM
examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.- ERROR 1118 (42000): Row size too large. The maximum row size for the used
- ERROR 1118 (42000): Row size too large. The maximum row size for the used
In the following
MyISAM
example, changing a column toTEXT
avoids the 65,535-byte row size limit and permits the operation to succeed becauseBLOB
andTEXT
columns only contribute 9 to 12 bytes toward the row size.- Query OK, 0 rows affected (0.02 sec)
The operation succeeds for an
InnoDB
table because changing a column toTEXT
avoids the MySQL 65,535-byte row size limit, andInnoDB
off-page storage of variable-length columns avoids theInnoDB
row size limit.Storage for variable-length columns includes length bytes, which are counted toward the row size. For example, a
VARCHAR(255) CHARACTER SET utf8mb3
column takes two bytes to store the length of the value, so each value can take up to 767 bytes.The statement to create table
t1
succeeds because the columns require 32,765 + 2 bytes and 32,766 + 2 bytes, which falls within the maximum row size of 65,535 bytes:- Query OK, 0 rows affected (0.02 sec)
The statement to create table
t2
fails because, although the column length is within the maximum length of 65,535 bytes, two additional bytes are required to record the length, which causes the row size to exceed 65,535 bytes:- ERROR 1118 (42000): Row size too large. The maximum row size for the used
Reducing the column length to 65,533 or less permits the statement to succeed.
For
MyISAM
tables,NULL
columns require additional space in the row to record whether their values areNULL
. EachNULL
column takes one bit extra, rounded up to the nearest byte.The statement to create table
t3
fails becauseMyISAM
requires space forNULL
columns in addition to the space required for variable-length column length bytes, causing the row size to exceed 65,535 bytes:- ERROR 1118 (42000): Row size too large. The maximum row size for the used
For information about
InnoDB
NULL
column storage, see Section 15.10, “InnoDB Row Formats”.InnoDB
restricts row size (for data stored locally within the database page) to slightly less than half a database page for 4KB, 8KB, 16KB, and 32KBinnodb_page_size
settings, and to slightly less than 16KB for 64KB pages.The statement to create table
t4
fails because the defined columns exceed the row size limit for a 16KBInnoDB
page.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-column-count-limit.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.