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

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

列數(shù)限制

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

1. 表的最大行大小限制了列的數(shù)量（和可能的大小），因?yàn)樗辛械目傞L(zhǎng)度不能超過(guò)這個(gè)大小。

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

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

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

行大小限制

給定表的最大行大小由幾個(gè)因素決定：

1. MySQL表的內(nèi)部表示具有最大行大小限制為65,535字節(jié)，即使存儲(chǔ)引擎能夠支持更大的行。BLOB和TEXT列只對(duì)行大小限制貢獻(xiàn)9到12個(gè)字節(jié)，因?yàn)樗鼈兊膬?nèi)容是分開(kāi)存儲(chǔ)的。

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

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

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

舉例說(shuō)明

示例1：行大小超限

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

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;

此命令將因?yàn)樾写笮〕^(guò)MySQL的最大限制而失敗，報(bào)錯(cuò)信息如下：

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é)行大小限制，因?yàn)锽LOB和TEXT列的存儲(chǔ)是分開(kāi)的，每個(gè)只占用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)建成功，顯示如何通過(guò)調(diào)整列類型來(lái)符合行大小限制。

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

對(duì)于InnoDB表，可變長(zhǎng)度數(shù)據(jù)超過(guò)最大行大小時(shí)，InnoDB會(huì)將部分?jǐn)?shù)據(jù)存儲(chǔ)到外部頁(yè)面。例如：

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列的外部存儲(chǔ)，這個(gè)表創(chuàng)建也是成功的，表明InnoDB對(duì)可變長(zhǎng)度列有特別的存儲(chǔ)策略，可以有效利用空間。

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

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

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

當(dāng)嘗試創(chuàng)建包含VARCHAR(65535)的列時(shí)，會(huì)因?yàn)槌鲂写笮《。驗(yàn)閂ARCHAR需要額外的字節(jié)來(lái)存儲(chǔ)長(zhǎng)度信息。調(diào)整列長(zhǎng)度至65533或更少，使得表創(chuàng)建成功。

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

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

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

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

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

最新評(píng)論