說說 VARCHAR 背後的那些事

iVictor發表於2021-08-14

在使用MySQL的過程中，在儲存字串時，大家或許都有過這樣或那樣的困惑，譬如：

1.  對於固定長度的字串，為什麼推薦使用 CHAR 來儲存？

2.  VARCHAR 可設定的最大長度是多少？

3.  給定一個字串，怎麼知道它的空間使用情況？

4.  建立索引時，提示“Index column size too large. The maximum column size is 767 bytes”，該如何解決？

5.  VARCHAR 為何要按需設定？VARCHAR(50) 和 VARCHAR(500) 有什麼區別？

下面就這些問題做一個系統的分析。

 

1. CHAR與VARCHAR的區別

兩者都可用來儲存字串。只不過 CHAR 常用來儲存固定長度的字串，VARCHAR 常用來儲存可變長度的字串。為什麼要這樣區分呢？

首先看下面這個表格。CHAR(4) 和 VARCHAR(4) 的儲存對比。

值CHAR(4)儲存需求（位元組）VARCHAR(4)儲存需求（位元組）
'' '    ' 4 '' 1
'ab' 'ab   ' 4 'ab' 3
'abcd' 'abcd' 4 'abcd' 5
'abcdefgh' 'abcd' 4 'abcd' 5

基於表格的內容，我們可以得出以下結論：

對於 CHAR(4) ，

1.  無論插入什麼值，儲存需求都是不變的，固定4個位元組。

2.  在實際儲存的時候，對於不足4位元組的值，右邊會以空格填充。

對於 VARCHAR(4) ，

1.  儲存的需求與插入的值有關。

2.  儲存的需求 = 字串所佔的位元組數 + 1。為什麼要加1呢？這個與 VARCHAR 的實現有關，為了實現“按需分配”的目的，它需要額外的位元組來表示字串的長度。

 

所以，對於固定長度的字串推薦使用 CHAR 來儲存，相對於 VARCHAR ，前者會少用一個位元組。

另外，'abcdefgh'被截斷為'abcd'進行儲存，是在 SQL_MODE 非嚴格模式下。具體什麼是 SQL_MODE 的非嚴格模式，可參考：使用MySQL，SQL_MODE有哪些坑，你知道麼？

 

2. VARCHAR(M) 能設定的最大長度

M 限制了 VARCHAR 能儲存的字串的最大長度，注意，是字元，不是位元組，其有效值範圍為 0 ~ 65535。雖然可設定的範圍是 0 ～ 65535，但 M 真的就能設定為65535 嗎？

看下面這個測試。

mysql> create table t (c1 varchar(65535)) charset 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 (c1 varchar(65534)) charset 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 (c1 varchar(65533)) charset 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 (c1 varchar(65532)) charset latin1;
Query OK, 0 rows affected (0.06 sec)

由此來看，在 latin1 字符集下，M 最大就只能設定為 65532。

 

其實不然，再看下面這個示例。

mysql> create table t (c1 varchar(65533) not null) charset latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t (c1 varchar(65534) not null) charset latin1;
Query OK, 0 rows affected (0.06 sec)

如果將列定義為NOT NULL，M 最大可設定為 65533。

 

上面演示的是latin1下的使用場景，如果是其它字符集呢？

mysql> create table t (c1 varchar(65533) not null) charset utf8mb4;
ERROR 1074 (42000): Column length too big for column 'c1' (max = 16383); use BLOB or TEXT instead

mysql> create table t (c1 varchar(65533) not null) charset utf8;
ERROR 1074 (42000): Column length too big for column 'c1' (max = 21845); use BLOB or TEXT instead

基於報錯資訊，可以看出，對於utf8mb4字符集，M最大隻能設定為16383。對於utf8字符集，M最大隻能設定為21845。這兩個數值是怎麼計算出來的呢？

在utf8mb4字符集中，最多需要4個位元組來表示一個字元，所以 65535 / 4 = 16383 。而在utf8字符集中，最多需要3個位元組來表示一個字元，所以 65535 / 3 = 21845。

由此來看，在設定 M 的大小時，起決定作用的並不是 M 的有效值範圍（0 ~ 65535），而是 M * 字符集的最大位元組數不能超過65535個位元組。

 

為什麼不能超過 65535 位元組呢？因為MySQL限制了一條記錄的最大長度就是 65535 位元組。

 

除此之外，對於VARCHAR，在實際設定時，還需考慮以下兩個因素：

1.  MySQL需要1 ～  2個位元組來表示字串的長度。具體來說，如果字串佔用的位元組數在 0 ～255 之間，需1個位元組來表示，如果大於 255 個位元組，則需2個位元組來表示。

2.  如果列定義為NULL，額外還需要1個位元組。

既然 65535 是記錄的最大長度，則這個限制不僅僅是針對一列，而是所有列。即所有列的長度加起來不能超過65535。

看下面這個示例，指定了2個列，分別定義為VARCHAR和INT。

mysql> create table t (c1 varchar(65530) not null,c2 int not null) charset 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 (c1 varchar(65529) not null,c2 int not null) charset latin1;
Query OK, 0 rows affected (0.10 sec)

因為INT會佔4個位元組，所以 c1 最大就只能設定為65535 - 4 - 2 = 65529。這裡之所以要減去2，是因為varchar(65529)超過了255個位元組，需要2個位元組來表示其長度。

 

報錯資訊中，提到“The maximum row size for the used table type, not counting BLOBs, is 65535”，即記錄的最大長度限制不包括BLOB等欄位。

之所以將BLOB和TEXT排除在外，是因為它的內容會單獨儲存在其它頁中。但即便如此，儲存BLOB和TEXT的指標資訊也需要9 ~ 12個位元組，具體來說：

  • TINYTEXT(TINYBLOB): 9 位元組
  • TEXT(BLOB): 10 位元組
  • MEDIUMTEXT(MEDIUMBLOB): 11位元組
  • LONGTEXT(LONGBLOB): 12位元組。

看下面這個示例，指定了2個列，分別定義為VARCHAR和TEXT。

mysql> create table t (c1 varchar(65524) not null,c2 text not null) charset 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 somecolumns to TEXT or BLOBs

mysql> create table t (c1 varchar(65523) not null,c2 text not null) charset latin1;
Query OK, 0 rows affected (0.13 sec)

因為TEXT佔了10個位元組，所以 c1 最大可設定為65535 - 10 - 2 = 65523。

 

注意，上面提到的一條記錄的最大長度不能超過65535位元組是MySQL的限制，與儲存引擎無關。實際上，儲存引擎對行長也有限制。在InnoDB儲存引擎中，就規定一條記錄的最大長度不能超過資料頁大小的1/2。

InnoDB中資料頁的大小由innodb_page_size引數決定，預設為16K。所以，在頁長為16K的情況下，InnoDB中一條記錄的大小不能超過8K。如果超過了8K，InnoDB會將部分變長欄位儲存在外部頁中。

 

看下面這個示例，因為定義的都是CHAR，定長欄位，所以不會儲存在外部頁中。

mysql> create table t (
            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)
            ) 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.

 

3. VARCHAR的空間使用情況

給定一個VARCHAR型別的字串，怎麼知道它的空間使用情況呢？

首先，看看官方文件的說法。

型別儲存需求
VARCHAR(M) L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes

這裡的 L 指的是字串所佔的位元組數，與字符集有關。

以“中國”為例。

在utf8mb4中，一箇中文漢字需要 3 個位元組來表示，所以，“中國”通過utf8mb4來儲存，會佔用6個位元組。

而在GBK中，一箇中文漢字只需2個位元組來表示，所以，“中國”通過GBK來儲存，會佔用4個位元組。

除此之外，還需要額外的1~2個位元組來表示字串的長度。

 

4. VARCHAR(50) 和 VARCHAR(500) 的區別

很多人可能會好奇，VARCHAR 不是按需分配的麼？在滿足業務的需求情況下，設定 VARCHAR(50) 和 VARCHAR(500) 有什麼區別呢？最後的空間佔用還不是以實際寫入的字串為主，從這個角度來看，確實沒錯。但考慮以下兩點：

1. 索引有最大長度的限制

對於行格式為 REDUNDANT 或 COMPACT 的InnoDB表，索引的最大長度被限制為767位元組。所以，在MySQL 5.6 中，在建立索引時，我們通常會碰到“Index column size too large. The maximum column size is 767 bytes”錯誤：

mysql> create table t(c1 varchar(200)) charset=utf8mb4;   
Query OK, 0 rows affected (0.02 sec)

mysql> alter table t add index(c1);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

上面這個限制不僅僅適用於單個索引，同樣也適用於複合索引。

要解決這個問題，可選的方案有：

1.  減少欄位的長度，確保欄位的長度 * 字符集的最大位元組數不超過767。

2.  設定字首索引，確保字首索引的長度 * 字符集的最大位元組數不超過767。

mysql> alter table t add index(c1(191));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.  將表的行格式設定為 DYNAMIC 或 COMPRESSED，同時開啟innodb_large_prefix，索引的最大長度可支援3072位元組。

在 MySQL 5.6 中，如果要將表的行格式設定為DYNAMIC 或 COMPRESSED，必須將引數innodb_file_format設定為Barracuda。

innodb_file_format用來設定InnoDB檔案的格式，支援的檔案格式有Antelope和Barracuda。Antelope是最早的檔案格式，支援REDUNDANT和COMPACT這兩種行格式，而Barracuda則是最新的檔案格式，支援DYNAMIC和COMPRESSED這兩種行格式。

在 MySQL 5.6 中，innodb_file_format預設為Antelope，建立表時，如果沒有顯式指定row_format，則預設為Compact。

在 MySQL 5.7 中，innodb_file_format預設為Barracuda，建立表時，如果沒有顯式指定row_format，則預設為Dynamic。

在 MySQL 8.0 中，innodb_file_format被移除了，取而代之的是innodb_default_row_format。該引數用來設定預設的row_format，預設值為dynamic。

而innodb_large_prefix呢？在 MySQL 5.6 中，預設為OFF。在 MySQL 5.7 中，預設為ON，在 MySQL 8.0中，也移除了。

這也是為什麼在 MySQL 5.6 中，更容易出現“Index column size too large. The maximum column size is 767 bytes”錯誤。

 

2. MEMORY引擎的限制

在 MySQL 8.0 之前，記憶體臨時表只支援 MEMORY 引擎，而 MEMORY 引擎會將 VARCHAR 等變長型別作為定長來分配記憶體。

When in-memory internal temporary tables are managed by the MEMORY storage engine, fixed-length row format is used. VARCHAR and VARBINARY column values are padded to the maximum column length, in effect storing them as CHAR and BINARY columns.

可喜的是，從 MySQL 8.0 開始，記憶體臨時表支援 TempTable 引擎。TempTable 引擎，從 MySQL 8.0.13 開始，優化了VARCHAR等變長型別的儲存。

 

5. 總結

1.  對於固定長度的字串推薦使用 CHAR 來儲存。

2.  VARCHAR能設定的最大長度與使用的字符集、自身佔用的位元組數、是否定義為NULL有關。

3.  MySQL限制了一條記錄的最大長度是 65535 位元組。

4.  InnoDB儲存引擎限制了一條記錄的最大長度不能超過資料頁大小的1/2。但在實際處理的時候，InnoDB會將部分變長欄位儲存在外部頁中，所以我們實際能儲存的比限制的要大。

5.  字串所佔的位元組數，與字符集有關。除此之外，還需要額外的1~2個位元組來表示字串的長度。

6.  在滿足業務需求的情況下，VARCHAR應越短越好。

7.  對於行格式為 REDUNDANT 或 COMPACT 的InnoDB表，索引的最大長度為767位元組。

8.  對於行格式為 DYNAMIC 或 COMPRESSED 的InnoDB表，索引的最大長度為3072位元組。

9.  MEMORY 引擎會將 VARCHAR 等變長型別作為定長來分配記憶體。

 

相關文章