MySQL表列數(shù)和行大小限制詳解

MySQL在表的列數(shù)和行大小方面有一些限制，本文將對這些限制進行詳細解釋。

列數(shù)限制

MySQL對每個表的列數(shù)有硬限制為4096列，但對于給定的表，實際的最大列數(shù)可能會更少。確切的列限制取決于幾個因素：

1. 表的最大行大小限制了列的數(shù)量（和可能的大小），因為所有列的總長度不能超過這個大小。

2. 單個列的存儲要求限制了可以容納在給定最大行大小內(nèi)的列數(shù)。某些數(shù)據(jù)類型的存儲要求取決于諸如存儲引擎、存儲格式和字符集等因素。

3. 存儲引擎可能會施加額外的限制，限制表列數(shù)。例如，InnoDB對每個表有1017列的限制。而MyISAM引擎則允許大約5000列。

4. 功能鍵部分（參見CREATE INDEX語句）被實現(xiàn)為隱藏的虛擬生成存儲列，因此表索引中的每個功能鍵部分都計入表的總列限制。

行大小限制

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

1. MySQL表的內(nèi)部表示具有最大行大小限制為65,535字節(jié)，即使存儲引擎能夠支持更大的行。BLOB和TEXT列只對行大小限制貢獻9到12個字節(jié)，因為它們的內(nèi)容是分開存儲的。

2. 對于InnoDB表，最大行大小適用于存儲在數(shù)據(jù)庫頁內(nèi)的數(shù)據(jù)，對于4KB、8KB、16KB和32KB的innodb_page_size設(shè)置，最大行大小略小于半個頁面。例如，默認16KB的InnoDB頁面大小的最大行大小略小于8KB。對于64KB頁面，最大行大小略小于16KB。

3. 如果包含可變長度列的行超過InnoDB最大行大小，則InnoDB會選擇將可變長度列存儲到外部頁面，直到行符合InnoDB行大小限制。存儲在外部的可變長度列的數(shù)據(jù)量因行格式而異。

4. 不同的存儲格式使用不同數(shù)量的頁面頭和尾數(shù)據(jù)，這影響可用于行的存儲量。

舉例說明

示例1：行大小超限

考慮以下嘗試在MySQL中創(chuàng)建一個表的情況：

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;

此命令將因為行大小超過MySQL的最大限制而失敗，報錯信息如下：

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

示例2：改變列類型以適應(yīng)行大小限制

將一些列改為TEXT類型，可以避免65,535字節(jié)行大小限制，因為BLOB和TEXT列的存儲是分開的，每個只占用9到12字節(jié)。示例如下：

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;

此表創(chuàng)建成功，顯示如何通過調(diào)整列類型來符合行大小限制。

示例3：存儲可變長度數(shù)據(jù)的優(yōu)化

對于InnoDB表，可變長度數(shù)據(jù)超過最大行大小時，InnoDB會將部分數(shù)據(jù)存儲到外部頁面。例如：

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;

由于TEXT列的外部存儲，這個表創(chuàng)建也是成功的，表明InnoDB對可變長度列有特別的存儲策略，可以有效利用空間。

示例4：列長和可存儲數(shù)據(jù)的關(guān)系

創(chuàng)建包含最大長度VARCHAR列的表：

CREATE TABLE t2 (
  c1 VARCHAR(65533) NOT NULL
) ENGINE = InnoDB CHARACTER SET latin1;

當(dāng)嘗試創(chuàng)建包含VARCHAR(65535)的列時，會因為超出行大小而失敗，因為VARCHAR需要額外的字節(jié)來存儲長度信息。調(diào)整列長度至65533或更少，使得表創(chuàng)建成功。

示例5：CHAR列和行大小限制

嘗試在InnoDB表中創(chuàng)建包含多個CHAR(255)列的表：

CREATE TABLE t4 (
  c1 CHAR(255), c2 CHAR(255), c3 CHAR(255),
  ... (多達33個CHAR(255)列) ...
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;

此嘗試將失敗，因為CHAR(255)列使得總行大小超過了16KB的InnoDB頁限制。這個例子表明，即使單個CHAR(255)只占用255字節(jié)，多列的累積也可能導(dǎo)致總行大小超限。

到此這篇關(guān)于MySQL表列數(shù)和行大小限制詳解的文章就介紹到這了,更多相關(guān)MySQL表列數(shù)和行大小內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論