MySQL的欄位數量以及長度限制

摸魚小天才發表於2023-09-20

一、InnoDB行格式

行格式	緊湊的儲存特性	增強的可變長度列儲存	大型索引鍵字首支援	壓縮支援	支援的表空間型別
REDUNDANT	N	N	N	N	system, file-per-table, general
COMPACT	Y	N	N	N	system, file-per-table, general
DYNAMIC	Y	Y	N	N	system, file-per-table, general
COMPRESSED	Y	Y	Y	Y	file-per-table, general

開發中常用的是DYNAMIC行格式，這裡著重對它進行介紹，其他格式說明請查閱官網。

1.1 COMPACT儲存特性

行DYNAMIC格式提供與行格式相同的儲存特性COMPACT，但增加了對長可變長度列的增強儲存功能，並支援大索引鍵字首

每個索引記錄包含一個 5 位元組的標頭，並且可能會有一部分的可變長度，用於將連續的記錄連結在一起，並用於行級鎖定;
記錄頭的可變長度部分包含一個用於指向NULL列的位向量。若索引中可以為 NULL的列數為N，則位向量佔用N/8個位元組數。（例如，如果有 9 到 16 列可以是，則位向量使用兩個位元組。）除了此向量中的位之外，別的為NULL的列並不佔用空間。標頭的可變長度部分還包含可變長度列的長度。每個長度佔用一個或兩個位元組，具體取決於列的最大長度。如果索引中的所有列都是NOT NULL並且具有固定長度，那麼記錄頭就沒有可變長度部分;
對於每個非NULL可變長度欄位，記錄頭包含一或兩個位元組的列長度。僅當部分列儲存在溢位頁外部或最大長度超過 255 位元組且實際長度超過 127 位元組時，才需要兩個位元組。對於外部儲存的列，2位元組長度表示內部儲存部分的長度加上指向外部儲存部分的20位元組指標。內部部分是768位元組，所以長度是768+20。20 位元組的指標儲存列的真實長度;
記錄頭之後是非NULL列的資料內容;
聚集索引中的記錄包含所有使用者定義列的欄位。此外，還有一個 6 位元組的事務 ID 欄位和一個 7 位元組的滾動指標欄位;
如果沒有為表定義主鍵，則每個聚簇索引記錄還包含一個 6 位元組的行 ID 欄位;
每個二級索引記錄包含了由聚集索引鍵定義的所有主鍵列，這些主鍵列不在二級索引中。如果任何主鍵列是可變長度的，則每個二級索引的記錄頭都有一個可變長度部分來記錄它們的長度，即使二級索引是在固定長度列上定義的;
在內部，對於非可變長度字符集，固定長度字元列如CHAR(10)，是以固定長度格式儲存的。並且不會從 VARCHAR型別的列中截斷尾隨空格;
在內部，對於諸如utf8mb3和utf8mb4之類的可變長度字符集， InnoDB嘗試透過修剪尾隨空格來將CHAR(N)儲存為N位元組。如果CHAR(N)列的位元組長度超過N位元組，則將尾隨空格修剪為該列位元組長度的最小值。CHAR(N)列的最大長度是最大字元位元組長度 × N;
至少為 CHAR(N) 保留 N 個位元組。在許多情況下，保留最小空間 N 可以使列就地更新，不會導致索引頁碎片。相比之下，當使用 REDUNDANT 行格式時，CHAR(N) 列佔用的長度是最大字元位元組長度 × N;
大於或等於 768 位元組的固定長度列被編碼為可變長度欄位，可以跨頁儲存。例如，如果字符集的最大位元組長度大於 3，則 CHAR(255) 列可以超過 768 個位元組，如utf8mb4;

1.2 DYNAMIC儲存特性

DYNAMIC 行格式提供與 COMPACT 行格式相同的儲存特性，但增強了可變長度的儲存功能，並支援大索引鍵字首;
當使用 ROW_FORMAT=DYNAMIC 建立表時，InnoDB 可以儲存長的可變長度列值（ VARCHAR、VARBINARY、BLOB 、 TEXT ）完全離頁，聚簇索引記錄僅包含一個 20 位元組的指標指向溢位頁。大於或等於 768 位元組的固定長度欄位被編碼為可變長度欄位;
列是否儲存在頁外取決於頁大小和行的總大小。當一行太長時，選擇最長的列進行頁外儲存，直到聚集索引記錄適合 B 樹頁。小於或等於 40 位元組的 TEXT 和 BLOB 列按行儲存;
DYNAMIC 行格式保持了將整行儲存在索引節點中的效率（如果適合）（COMPACT 和 REDUNDANT 格式也是如此），但是 DYNAMIC 行格式避免了用大量資料位元組填充 B 樹節點的問題的長列。 DYNAMIC 行格式基於這樣的想法，即如果長資料值的一部分儲存在頁外，則通常將整個值儲存在頁外是最有效的。使用 DYNAMIC 格式，較短的列可能會保留在 B 樹節點中，從而最大限度地減少給定行所需的溢位頁數;
DYNAMIC 行格式支援最多 3072 位元組的索引鍵字首;
使用 DYNAMIC 行格式的表可以儲存在system tablespace、file-per-table tablespace和general tablespace中。要在system tablespace中儲存動態表，請禁用 innodb_file_per_table 並使用常規 CREATE TABLE 或 ALTER TABLE 語句，也可以搭配 TABLESPACE [=] innodb_system 。 innodb_file_per_table 變數不適用於一般表空間，在使用 TABLESPACE [=] innodb_system 表選項在system tablespace中儲存 DYNAMIC 表時也不適用;

二、資料型別儲存要求

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

2.1 資料型別儲存要求

DECIMAL（和 NUMERIC）列的值使用二進位制格式表示，該格式將九個十進位制（基數 10）數字打包成四個位元組。每個值的整數和小數部分的分開儲存。九個十進位制數字的每個倍數需要四個位元組，“剩餘”數字需要四個位元組的一部分。下表給出了多餘數字所需的儲存空間：

2.2 日期時間型別儲存要求

MySQL5.6.4 之後建立的表允許TIME、DATETIME 和 TIMESTAMP這些型別具有小數部分，這額外需要 0 到 3 個位元組，具體取決於儲存值的小數秒精度：

2.3 String型別儲存要求

M：非二進位制字串型別的宣告列長度（以字元為單位）和二進位制字串型別的位元組數；
N：給定字串值的實際長度（以位元組為單位）；

可變長度字串型別使用一個帶長度的字首+資料儲存。字首的長度根據資料型別以及字首的值 L（字串的位元組長度）來確定需要多少位元組(1-4)，。例如，MEDIUMTEXT 值的儲存需要 L 個位元組，加上三個位元組來儲存該值的長度；
要計算用於儲存特定 CHAR、VARCHAR 或 TEXT 列的位元組數，必須考慮該列的字符集以及是否包含多位元組字元。特別是，在使用 UTF-8 Unicode 字符集時，必須記住並非所有字元都使用相同的位元組數。 utf8mb3 和 utf8mb4 字符集可能分別要求每個字元需要三個和四個位元組；
VARCHAR、VARBINARY 、 BLOB、 TEXT是可變長度型別，儲存要求取決於以下因素：

列值的實際長度
列的最大可能長度
列使用的字符集，因為有些字符集包含多位元組字元

例如，VARCHAR(255) 列可以容納最大長度為 255 個字元的字串。假設該列使用latin1字符集（每個字元一個位元組），實際需要儲存的是字串的長度（L），加上一個位元組來記錄字串的長度。對於字串 'abcd'，L 為 4，儲存要求為 5 個位元組。如果同一列改為宣告使用 ucs2 雙位元組字符集，則儲存要求為 10 個位元組：'abcd' 的長度為八個位元組，該列需要兩個位元組來儲存長度，因為最大長度大於 255 （最多 510 個位元組）；
可以儲存在 VARCHAR 或 VARBINARY 列中的最大有效位元組數受行最大為 65535 位元組的限制，該行大小在所有列之間共享。對於儲存多位元組字元的 VARCHAR 列，有效的最大字元數更少。例如，utf8mb4 字元每個字元最多可以使用四個位元組，因此使用 utf8mb4 字符集的 VARCHAR 列最多可宣告為 16,383 個字元；
ENUM 物件的大小由不同列舉值的數量決定。一個位元組用於具有最多 255 個可能值的列舉。兩個位元組用於具有 256 到 65,535 個可能值的列舉；
SET 物件的大小由不同集合成員的數量決定。如果設定大小為 N，則物件佔用 (N+7)/8 個位元組，四捨五入為 1、2、3、4 或 8 個位元組。一個 SET 最多可以有 64 個成員；

三、表列數和行大小的限制

https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html

3.1 列數限制

MySQL 規定每個表只能有 4096 列，但對於給定的表，有效最大值可能會更少。確切的列限制取決於幾個因素：

表的最大行大小限制了列的數量（可能還有列大小），因為所有列的總長度不能超過此大小；
各個列的儲存要求限制了行最大固定了的列數。某些資料型別的儲存要求取決於儲存引擎、儲存格式和字符集等因素；
儲存引擎可能會施加額外的限制來限制表的列數。例如，InnoDB 的每個表有 1017 列的限制；
功能鍵部分被實現為隱藏的虛擬生成儲存列，因此表索引中的每個功能鍵部分都計入表總列限制

3.2 行大小限制

表的最大行大小由幾個因素決定：

MySQL 表內部有 65535 位元組的最大行大小限制，即使儲存引擎能夠支援更大的行也是65535。 BLOB 和 TEXT 雖然是大文字，但是由於它們的內容與行的其餘部分分開儲存，因此它們的列僅佔9 到 12 個位元組；
InnoDB 表的最大行大小適用於本地儲存在資料庫頁面中的資料，在對 4KB、8KB、16KB 和 32KB innodb_page_size 設定中略小於半頁。例如，對於預設的 16KB InnoDB 頁面大小，最大行大小略小於 8KB。對於 64KB 頁面，最大行大小略小於 16KB；
如果包含可變長度列的行超過 InnoDB 最大行大小，則 InnoDB 選擇外部頁外儲存的可變長度列，直到該行符合 InnoDB 行大小限制。對於頁外儲存的可變長度列，本地儲存的資料量因行格式而異；
不同的儲存格式使用不同數量的頁首和頁尾資料，會影響可用於行的儲存量；

3.3 行大小限制案例

1）在以下 InnoDB 和 MyISAM 示例中演示了 65,535 位元組的 MySQL 最大行大小限制。無論儲存引擎如何，都會強制執行該限制，即使儲存引擎可能能夠支援更大的行：

-- InnoDB引擎
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

-- MyISAM 引擎
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

列更改為 TEXT 可避免 65535 位元組的行大小限制，因為 BLOB 和 TEXT 列僅佔 9 到 12 位元組，並且 InnoDB 變長列的頁外儲存避免了 InnoDB 行大小限制：

-- MyISAM中TEXT的使用
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的使用
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)

2) 可變長度列的儲存包括計入行大小的長度位元組。例如，VARCHAR(255) CHARACTER SET utf8mb3 列需要兩個位元組來儲存值的長度，因此每個值最多可以佔用 767 個位元組:

latin1+InnaDB:
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)

使用的是Latin1，一個字元一個位元組，因此列需要 32765+2 + 32766+2 位元組 <  65535；

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

65535 + 2 > 65535，因此建立失敗;

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

65533 + 2 = 65535，建立成功

3）對於 MyISAM 表，NULL 列需要在行中額外的空間來記錄它們的值是否為 NULL。每個 NULL 列多佔用一位，四捨五入到最接近的位元組

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

32765 + 2 + 32766 + 2 = 65535，但是對於MyISAM還需要額外的空間來儲存null值，因此建立失敗

4）對於 4KB、8KB、16KB 和 32KB innodb_page_size 設定，InnoDB 將行大小（對於本地儲存在資料庫頁面中的資料）限制為略小於資料庫頁面的一半，而對於 64KB 頁面限制為略小於 16KB

tip: 對於64KB的一半為什麼是16，目前也沒在官方文件找到較為詳細的解釋，只有下面的解釋：

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.

以上例子使用InnoDB的預設頁大小16KB，因此最大行大小會限制在8KB以下，即 8 * 1024 = 8192，由於是略小於，從報錯資訊中也可以發現，真正的行大小是8126
而 33 * 255 = 8415 > 8126，因此建立失敗。

結語

這塊內容是我在MySQL官網上翻譯總結而來，內容比較枯燥，並且實際開發中也並不會注意這麼多，因為大多數業務表並不會離譜道到超出欄位數量及行大小限制。不過在認真閱讀的時候也確實對MySQL有了更深的認識，在以後建立表的時候也會有意識無意識的想到相關的規約，這對於之後的開發或糾錯或多或少都會有幫助，等真正出現該類問題時至少不會手足無措。如果你不想看官網的全英文文件，就簡單看看這裡的介紹，當然，更建議仔細閱讀官網，其內容會更加詳盡。

[20210423]建立檢視以及欄位長度.txt
2021-04-23
欄位長度前後端是否都需要做限制？
2024-03-07
後端
mysql的varchar欄位最大長度真的是65535嗎？
2023-04-10
MySql
pbootcms網站欄目url字數長度限制的修改方法
2024-10-25
boot網站
oracle 修改表欄位的長度
2020-10-31
Oracle
MySQL中修改一個資料庫下包含有某個相同欄位的所有表的欄位長度
2024-07-10
MySql資料庫
[20180613]縮短欄位長度.txt
2018-06-13
MySQL：查詢欄位數量多少對查詢效率的影響
2019-12-13
MySql
Oracle 修改欄位型別和長度
2020-04-05
Oracle型別
MySQL學習筆記4：完整性約束限制欄位
2021-09-09
MySql筆記
MySQL 5.6使用pt-online-schema-change線上修改大表欄位長度
2020-09-21
MySql
iOS TextFiled,TextView 長度限制，表情限制
2019-03-21
iOSTextView
ORANCLE 資料已存在，修改欄位型別長度
2024-11-26
型別
VBAP和VBEP的幾個數量欄位的說明
2018-08-16
MySQL欄位的取值範圍
2021-01-26
MySql
MySQL 欄位約束
2022-06-06
MySql
MySQL欄位新增註釋，但不改變欄位的型別
2020-10-19
MySql型別
MySQL VARCHAR型別欄位到底可以定義多長
2020-09-24
MySql型別
abc欄位數的使用
2024-05-21
MySQL查詢某個欄位含有字母數字的值
2024-05-24
MySql
[BUG反饋]username欄位的長度設定有點小問題。
2020-04-04
vue---input只能輸入數字，且限制長度
2024-05-24
Vue
DDL操作導致欄位長度變更修復方案
2023-03-16
java統計實體類中空欄位數量
2024-11-27
Java
mysql 字串型別的數值欄位按照數值的大小進行排序
2018-12-19
MySql字串型別排序
MySQL 中 JSON 欄位的使用技巧
2018-12-12
MySqlJSON
MySQL中JSON欄位的使用技巧
2018-12-14
MySqlJSON
MySQL如何檢視新增修改表以及欄位註釋資訊
2024-02-28
MySql
《MySQL 入門教程》第 11 篇限制結果數量
2022-02-04
MySql
MySQL如何查詢某個欄位含有字母數字的值
2024-05-26
MySql
MySQL-刪除欄位
2018-11-29
MySql
MySQL 欄位擷取拼接
2024-05-21
MySql
MySQL 大欄位問題
2022-05-31
MySql
要慎用mysql的enum欄位的原因
2021-09-09
MySql
OGG複製同步,提示欄位長度不夠ORA-01704
2020-08-27
MySQL-去掉不為null的欄位
2019-01-09
MySqlNull
5_MySQL 表的欄位約束
2020-11-19
MySql
關於建立索引的 767 bytes 長度限制
2018-03-28
索引