Let's talk about the size and length limits of various objects in MySQL

Today I will introduce to you the size, length, and quantity limits of various objects in MySQL 8.0.

Identifier length limit

The following table describes the maximum lengths of various types of identifiers.

The alias of a field in a CREATE VIEW statement allows a maximum of 64 characters instead of 256 characters.

If you do not specify a constraint name when defining a constraint, MySQL automatically generates an internal name based on the name of the associated table. For example, automatically generated foreign key and CHECK constraint names consist of the table name plus _ibfk_ or _chk_ and a number. If the length of the table name is close to the limit of the constraint name (64 characters), it may be too long after adding other characters, resulting in an error.

Identifiers are stored using Unicode (UTF-8), including identifiers in table definitions and identifiers stored in privilege tables in the mysql database. The size of the identifier field in the permission table is in characters. We can use multi-byte characters (such as Chinese) as identifiers and the allowed length will not be affected.

In releases prior to NDB 8.0.18, NDB Cluster allowed database and table names to be up to 63 characters long. This limitation has been removed since NDB 8.0.18.

The user name and host name in MySQL account names are strings, not identifiers. Refer to the next section for the maximum length of these fields in the permission table.

Length limit of range field in permission table

The scope field in the permission table is used to store strings, and the default value is an empty string. The following table lists the maximum string lengths allowed to be stored in these fields.

The Host and Proxied_host strings are converted to lower case before storage.

For the purpose of checking access permissions, the comparison of the User, Proxied_user, authentication_string, Db, and Table_name strings is case-sensitive. Comparisons of the Host, Proxied_host, Column_name, and Routine_name strings are not case-sensitive.

Limitation on the number of databases and tables

MySQL does not limit the number of databases. However, the underlying file system may limit the number of directories that can correspond to one database.

MySQL does not limit the number of tables. However, the underlying file system may limit the number of files that a table can store. Different storage engines may have special restrictions. InnoDB allows the creation of 4 billion tables.

Table size limit

The size of a table in a MySQL database actually depends on the operating system file size limit, not the MySQL internal limit.

For Windows users, FAT and VFAT (FAT32) file systems are not suitable for use in production environments. It is recommended to use the NTFS file system to store MySQL databases.

If the table is full error occurs, the possible reasons are as follows:

• The disk is full.
• The storage engine is InnoDB and the tablespace file is full. The maximum size of a tablespace is also the maximum size of a table. For details, please refer to the size limit of the InnoDB storage engine. Generally speaking, it is recommended to consider partitioning tables across tablespace files only when the size of the table exceeds 1 TB.
• The operating system file size limit was reached. For example, the operating system supports a maximum file size of 2 GB, and when using the MyISAM storage engine, the data file or index file reaches this limit.

You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 − 1 bytes).

If you need a MyISAM table that is larger than the default limit and your operating system supports large files, the CREATE TABLE statement supports AVG_ROW_LENGTH and MAX_ROWS options. See CREATE TABLE Statement. The server uses these options to determine how large a table to permit.

If the pointer size is too small for an existing table, you can change the options with ALTER TABLE to increase a table's maximum permissible size. See ALTER TABLE Statement.

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

You have to specify AVG_ROW_LENGTH only for tables with BLOB or TEXT columns; in this case, MySQL cannot optimize the space required based only on the number of rows.

To change the default size limit for MyISAM tables, set the myisam_data_pointer_size, which sets the number of bytes used for internal row pointers. The value is used to set the pointer size for new tables if you do not specify the MAX_ROWS option. The value of myisam_data_pointer_size can be from 2 to 7. For example, for tables that use the dynamic storage format, a value of 4 permits tables up to 4GB; a value of 6 permits tables up to 256TB. Tables that use the fixed storage format have a larger maximum data length. For storage format characteristics, see MyISAM Table Storage Formats.

You can check the maximum data and index sizes by using this statement:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

You also can use myisamchk -dv /path/to/table-index-file. See SHOW Statements, or myisamchk — MyISAM Table-Maintenance Utility.

Other ways to work around file-size limits for MyISAM tables are as follows:

If your large table is read only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. myisampack also can merge multiple tables into a single table. See myisampack — Generate Compressed, Read-Only MyISAM Tables.

MySQL includes a MERGE library that enables you to handle a collection of MyISAM tables that have identical structure as a single MERGE table. See The MERGE Storage Engine.

You are using the MEMORY (HEAP) storage engine; in this case you need to increase the value of the max_heap_table_size system variable. See Server System Variables.

Limitations on the number of fields and row size

Field quantity limit

Each table in MySQL contains a maximum of 4096 fields, but the actual limit on the number of fields is smaller than this value. This limit depends on several factors:

1. The maximum size of a data row limits the number (and possible size) of fields, since the combined length of all fields cannot exceed this size. See next section.
2. The storage requirements of each field limit the number of fields. The storage requirements for certain data types depend on factors such as the storage engine, storage format, and character set. For details, please refer to the official documentation.
3. Storage engines may impose additional limits on the number of columns in a table. For example, InnoDB tables allow a maximum of 1017 columns. For other storage engine-related limitations, please refer to the official documentation.
4. The implementation of function indexes utilizes the hidden virtual computed storage column function, so each function index also occupies a field number.

Data row size limit

The size limit of a row of data is determined by the following factors:

• The internal storage limit for each row of data in a MySQL table is 65535 bytes, even if the storage engine can support larger storage. BLOB and TEXT types take up only 9 to 12 bytes because their actual contents are stored separately.
• The maximum size of an InnoDB table data row (data stored locally on a database page) is slightly less than half of innodb_page_size (4KB, 8KB, 16KB, and 32KB). For example, for the default 16KB page size configuration, the maximum size of a data row is slightly less than 8KB. For 64KB data pages, the maximum data row size is slightly less than 16KB.
  If the variable-length fields in a row exceed the InnoDB data row size limit, InnoDB will use off-page storage to save some variable-length fields until the data row can meet the InnoDB data row size limit. For variable-length fields stored off-page, the data content stored locally depends on the format of the data row. For more information, see "InnoDB Data Row Format".
• Different storage engines use different page header and footer data, which affects the actual storage space available for data rows.

Data row size limit example

The following InnoDB and MyISAM examples demonstrate the MySQL maximum row size limitation of 65535 bytes. This limit is independent of the storage engine and must be followed even if the storage engine can support larger data rows.

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
The table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
The table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

The following MyISAM example modifies one of the fields to TEXT, which avoids exceeding the 65535-byte limit because BLOB and TEXT fields only take up 9 to 12 bytes in the data row size.

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

The following InnoDB table can be created successfully because the field is modified to TEXT to avoid exceeding the 65535-byte limit, and InnoDB off-page storage can avoid exceeding the InnoDB data row size limit.

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

The storage of variable-length fields includes length information, which is also calculated into the data row size. For example, a VARCHAR(255) CHARACTER SET utf8mb3 field requires 2 bytes to store the length of the data, so each value may occupy up to 767 bytes.

The following statement successfully creates table t1 because its fields require 32765 + 2 bytes plus 32766 + 2 bytes, which satisfies the 65535-byte limit:

mysql> CREATE TABLE t1
       (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

The following statement fails to create table t2 because, although the length of the field does not exceed the 65535-byte limit, it exceeds the limit after adding 2 bytes to the record length:

mysql> CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
The table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs
Reducing the column length to 65,533 or less permits the statement to succeed.

mysql> CREATE TABLE t2
       (c1 VARCHAR(65533) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

For MyISAM tables, nullable fields require an extra space to record whether the data is null. Each nullable field requires 1 extra bit, which is rounded up to bytes.

The following statement fails to create table t3 because MyISAM requires additional space to store nullable fields, causing the data row size to exceed 65535 bytes:

mysql> CREATE TABLE t3
       (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
       ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
The table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs
For information about InnoDB NULL column storage, see InnoDB Row Formats.

InnoDB limits the row size (local data stored within a database page) to slightly less than half the size of a database page. The following statement fails because the length of all fields exceeds the data row size limit of 16 KB for an InnoDB page.

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.

Summarize

This is the end of this article about the size and length limits of various objects in MySQL. For more information about the size and length limits of MySQL objects, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!