深入聊聊MySQL中各種對象的大小長度限制

今天給大傢介紹一下 MySQL 8.0 中的各種對象的大小、長度以及數量限制。

標識符的長度限制

下表描述瞭各種不同類型標識符的最大長度。

CREATE VIEW 語句中字段的別名最多允許 64 個字符，而不是 256 個字符。

如果定義約束時沒有指定約束名，MySQL 自動基於相關的表名生成一個內部名稱。例如，自動生成的外鍵和 CHECK 約束名由表名加上 _ibfk_ 或者 _chk_ 以及一個數字組成。如果表名的長度接近約束名的長度限制（64 個字符），加上其他字符之後可能會超長，從而導致錯誤。

標識符使用 Unicode（UTF-8）存儲，包括表定義中的標識符和 mysql 數據庫中權限表中存儲的標識符。權限表中標識符字段的大小使用字符做為單位。我們可以使用多字節字符（例如中文）作為標識符，允許的長度不會受到影響。

NDB 8.0.18 之前的版本中，NDB Cluster 允許的數據庫名和表名最長為 63 個字符。從 NDB 8.0.18 開始刪除瞭這個限制。

MySQL 賬戶名中的用戶名和主機名是字符串，而不是標識符。關於權限表中的這些字段的最大長度參考下一節內容。

權限表中范圍字段的長度限制

權限表中的范圍字段用於存儲字符串，默認值為空字符串。下表列出瞭這些字段允許存儲的最大字符串長度。

Host 和 Proxied_host 字符串在存儲之前會轉換為小寫形式。

為瞭檢查訪問權限，User、Proxied_user、authentication_string、Db 以及 Table_name 字符串的比較區分大小寫。Host、Proxied_host、Column_name 以及 Routine_name 字符串的比較不區分大小寫。

數據庫和表的數量限制

MySQL 不限制數據庫的數量。不過，底層文件系統可能會限制目錄（一個目錄對應一個數據庫）的數量。

MySQL 不限制表的數量。不過，底層文件系統可能會限制文件（表使用文件存儲）的數量。不同存儲引擎可能存在特殊的限制，InnoDB 允許創建 40 億個表。

表大小的限制

MySQL 數據庫中表的大小實際上取決於操作系統文件大小的限制，而不是 MySQL 內部的限制。

對於 Windows 用戶而言，FAT 和 VFAT (FAT32) 文件系統不適合在生產環境中使用，推薦使用 NTFS 文件系統存儲 MySQL 數據庫。

如果出現表容量已滿的錯誤，可能的原因有以下幾點：

• 磁盤已滿。
• 存儲引擎為 InnoDB，表空間文件已滿。表空間的最大大小同樣也是表的最大大小。具體內容可以參考 InnoDB 存儲引擎的大小限制。一般而言，當表的大小超過 1 TB 時才推薦考慮使用跨表空間文件的分區表。
• 到達操作系統文件大小限制。例如，操作系統最大支持 2 GB 的文件，同時使用瞭 MyISAM 存儲引擎，數據文件或者索引文件到達瞭這個限制。

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.

字段數量和數據行大小的限制

字段數量限制

MySQL 中每個表最多包含 4096 個字段，不過實際上的字段數量限制比這個值更小。這個限制取決於幾個因素：

1. 數據行的最大大小限制瞭字段的數量（以及可能的大小），因為所有字段的總長度不能超過這個大小。參見下一節。
2. 每個字段的存儲需求限制瞭字段的數量。某些數據類型的存儲需求取決於存儲引擎、存儲格式以及字符集等因素。具體內容可以參考官方文檔。
3. 存儲引擎可能會額外限制表中字段的數量。例如，InnoDB 表最多允許 1017 個字段。其他存儲引擎相關的限制可以參考官方文檔。
4. 函數索引的實現利用瞭隱藏的虛擬計算存儲列功能，因此每個函數索引也會占用一個字段數量。

數據行大小限制

一行數據的大小限制有以下幾個因素決定：

• MySQL 表中每一行數據的內部存儲上限是 65535 字節，即使存儲引擎可以支持更大的存儲。BLOB 和 TEXT 類型隻占 9 到 12 個字節，因為它們的實際內容是單獨存儲的。
• InnoDB 表數據行（數據庫頁本地存儲的數據）的最大大小略小於 innodb_page_size（4KB、8KB、16KB 以及 32KB）的一半。例如，對於默認的 16KB 頁大小配置，數據行的最大大小為略少於 8KB。對於 64KB 數據頁，最大的數據行大小略小於 16KB。
  如果一行中的變長字段超過瞭 InnoDB 數據行大小限制，InnoDB 會使用頁外（off-page）存儲的方式保存某些變長字段，直到數據行能夠滿足 InnoDB 數據行大小限制。對於頁外存儲的變長字段，本地存儲的數據內容取決於數據行的格式，詳細信息可以參考“InnoDB 數據行格式”。
• 不同存儲引擎使用不同的頁頭和尾部數據，從而會影響到數據行實際可用的存儲空間。

數據行大小限制示例

以下 InnoDB 和 MyISAM 示例演示瞭 MySQL 最大行大小 65535 字節的限制。該限制和存儲引擎無關，即使存儲引擎可以支持更大的數據行，也需要遵循該限制。

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
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
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

以下 MyISAM 示例將其中一個字段修改為 TEXT，可以避免超過 65535 字節的限制，因為 BLOB 和 TEXT 字段隻在數據行大小中占用 9 到 12 個字節。

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)

以下 InnoDB 表可以創建成功是因為將字段修改為 TEXT 可以避免超過 65535 字節的限制，同時 InnoDB 頁外存儲可以避免超過 InnoDB 數據行大小的限制。

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)

變長字段的存儲包含瞭長度信息，這個內容也會被計算到數據行大小中。例如，一個 VARCHAR(255) CHARACTER SET utf8mb3 字段需要使用 2 個字節存儲數據的長度，因此每個數值最多可能占用 767 個字節。

以下語句能夠成功創建表 t1，因為它的字段需要 32765 + 2 字節加上 32766 + 2 字節，能夠滿足 65535 字節的限制：

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)

以下語句創建表 t2 失敗，因為雖然字段的長度沒有超過 65535 字節的限制，但是增加 2 個記錄長度的字節之後超過瞭該限制：

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
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)

對於 MyISAM 表，可空字段需要占用一個額外的空間記錄數據是否為空值。每個可空字段需要 1 個額外的比特，最終向上舍入到字節。

以下語句創建表 t3 失敗，因為 MyISAM 需要額外的空間存儲可空字段，從而導致數據行大小超過瞭 65535 字節：

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
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 限制行大小（數據庫頁內存儲的本地數據）為略小於數據庫頁的一半。以下語句失敗的原因是全部字段長度超過瞭一個 InnoDB 頁 16 KB 的數據行大小限制。

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.

總結

到此這篇關於MySQL中各種對象的大小長度限制的文章就介紹到這瞭,更多相關MySQL對象大小長度限制內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：

• Mysql中varchar類型一些需要註意的地方
• MySQL中你可能忽略的COLLATION實例詳解
• mysql數據庫表增添字段,刪除字段,修改字段的排列等操作
• 徹底解決MySQL使用中文亂碼的方法
• 詳細談談MYSQL中的COLLATE是什麼