MySQL 中 VARCHAR 最大長度及 CHAR 和 VARCHAR 的區別

問題

建立一個9個欄位的表，所有的欄位都為VARCHAR型別，其中前8個欄位都是最大能放下7279個字元，允許為空，字符集型別為latin1，即一個字元只佔一個位元組，建表語句如下：

CREATE TABLE test_varchar_length6(
    a VARCHAR(7279),
    b VARCHAR(7279),
    c VARCHAR(7279),
    d VARCHAR(7279),
    e VARCHAR(7279),
    f VARCHAR(7279),
    g VARCHAR(7279),
    h VARCHAR(7279),
    i VARCHAR(?)
)DEFAULT CHARSET=latin1

那麼i的最大字元數為多少？問題最後揭曉。

VARCHAR 定義

VARCHAR是變長字串，便於理解，使用CHAR定長型別來對比介紹。

• VARCAHR，最多儲存4個字元，有幾個字元儲存幾個。儲存位元組數=資料值的位元組和+1位元組(長度標識，後面會講到)
• CHAR(4)，最多儲存4個字元，不足4個尾部用空格填滿。儲存位元組數=資料值的位元組和+補位空格數

概括地說，VARCHAR和CHAR都是 MySQL 的字元型別，儲存多個字元、可設定最大儲存的字元數，儲存開銷與資料長度、字符集有關。是MySQL 最常用的字串型別。
CHAR和VARCHAR具體對比：

• 如果開啟PAD_CHAR_TO_FULL_LENGTH模式，檢索時尾部空格不會去除
• CHAR超過255字元會報錯，提示使用TEXT或BLOB：

ERROR 1074 (42000): Column length too big for column ''long_char''     (max = 255); use BLOB or TEXT instead

VARCHAR 的最大長度

在MySQL官方定義中，常用的 COMPACT、DYNAMIC行 模式下，最大長度受幾個因素影響：

• 行儲存的最大位元組數
• 資料之外的儲存開銷，官方定義中包括：NULL標識、長度標識
• 儲存字元的字符集

最大長度(字元數) = （行儲存最大位元組數 - NULL標識列佔用位元組數 - 長度標識位元組數） / 字符集單字元最大位元組數。有餘數時向下取整。

最大行大小

MySQL 行預設最大 65535 位元組，是所有列共享的，所以 VARCHAR 的最大值受此限制。
下面我們建立一個 65535 位元組的 VARCHAR，來驗證這個邊界值。

前面講過，VARCHAR宣告的長度是指字元數。要換算為65536位元組，最好一個字元只佔一個位元組。這裡測試使用的是MySQL 8.0，DEFAULT CHARSET=utf8mb4，所以這裡使用了latin1字符集。

CREATE TABLE test_varchar_length(
    `v` VARCHAR(65536) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=latin1

> 1074 - Column length too big for column 'v' (max = 65535); use BLOB or TEXT instead

可以看到報錯了，提示我們行最大長度為 65535 位元組。
如果我們要插入一個非空的 VARCHAR，其最大長度不能超過 65535（行最大值）-2（長度標識位）=65533位元組（長度標識位需兩位元組才能標識216=65535個數字）：

-- 測試邊界值65535，確認仍然過大；注意這裡使用字符集latin1，單位元組字符集
CREATE TABLE test_varchar_length(
    v VARCHAR(65534) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=latin1

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

-- 測試邊界值65533，建立成功，說明行最大值為65535
CREATE TABLE test_varchar_length(
    v VARCHAR(65533) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=latin1

> OK

-- 檢視字符集，是latin1，每個字元只佔用1個位元組
SHOW CREATE TABLE test_varchar_length;

可控列標識位

在COMPACT、DYNAMIC行格式下，行大小除了資料列長度，還包括可空列標識，即NULL標識位。

• 如果有一個列允許為空，則需要1 bit來標識，每8 bits的標識會組成一個欄位，該欄位會存放在每行最開始的位置。

  注意這個標識位不是放在每列，而是每行共享。

• 假設一張表中存在N個可空欄位，NULL標識位需要[N/8]（向上取整）個位元組。此時整行可用於資料儲存的空間只有65535-[N/8]個位元組。

下面通過例項來驗證一下：
在行大小的例子中，我們知道最大可建立 65535 位元組長度的非空 VARCHAR列。現在要建立一個可空列，每行需要1 bit的 NULL 標識位、MySQL會將其組裝成 1 byte的欄位存放，那麼我們應該建立最大為65533（最大非空VARCHAR列）-1(NULL標識列)=65532位元組的可空VARCHAR列：

-- 測試邊界值65533，確認仍然過大；注意這裡使用預設字符集latin1、單位元組字符集
CREATE TABLE test_varchar_length3(
    v VARCHAR(65533)
)ENGINE=INNODB DEFAULT CHARSET=latin1

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

-- 測試邊界值65532，建立成功，說明可空標識列確實佔去了1位元組；注意這裡使用預設字符集latin1、單位元組字符集
CREATE TABLE test_varchar_length3(
    v VARCHAR(65532)
)ENGINE=INNODB DEFAULT CHARSET=latin1

> OK

計算VARCHAR的最大長度，可空標識位是最容易忽略的。

字符集的單位元組符最大位元組數

字符集單字元最大位元組數不難理解，列舉MySQL常見的三個字符集：

• GBK:單位元組最大可佔用2個位元組。
• UTF8:單位元組最大可佔用3個位元組。
• UTF8MB4:單位元組最大佔4個位元組。
  假設還有6位元組可以存放字元，按字元佔用最大位元組數來算，可以存放3個GBK、2個UTF8、1個UTF8MB4。

VARCHAR 的長度標識位

長度標識位是相對比較複雜的，網上的介紹錯的很多，也容易算錯。
其作用是記錄資料的 位元組數。

儲存開銷是小於255只要1位元組、大於255後使用兩位元組。是因為按照可能的資料大小，分為0 ~ 255(2<sup>8</sup>)、256 ~ 65535(2<sup>16</sup>)，剛好對應1位元組和2位元組。>16)，剛好對應1位元組和2位元組。

但要注意，其計算根據的是欄位宣告的字元長度、計算可能的位元組數，再決定長度標誌的位元組數。如`VARCHAR(100)`，字符集為`UTF8`，可能的位元組數為300，長度標識則為2位元組。這是網上介紹錯的最多的。

另外長度標誌位只是儲存開銷，不影響佔用宣告的字元長度。宣告的字元長度的是資料的字元數，允許的最大字元數與字符集有關。

以VARCHAR(1)為例，可以存1個字元，MySQL會額外找一個位元組存放長度標識

樣例

公式應該都理解了：VARCHAR的最大長度=（最大行大小（65535）- NULL標識列佔用位元組數-長度標識位元組數）/字符集單位元組最大位元組數。有餘數向下取整。

接下來通過實驗來驗證，為了便於理解計算，例子做了一些調整：

• 不設定可控列、這樣可以去掉NULL標識列
• 為了便於體現長度標識位的差距，採用多了列的形式放大其存在
• 為了體現按可能位元組數計算長度，這裡採用多位元組的字符集GBK

建立一個表，包含2個非空VARCHAR(127)，每個列可能的最大位元組數為254、長度標識位是1位元組。那麼還可以新增最大65535-12722（2個列，每個列最大佔1272個位元組）-12（2個長度標識位）=65023位元組非空VARCHAR列，約等於32511個字元：

-- 測試邊界值32512，確認仍然過大
CREATE TABLE test_varchar_length4(
    v1 VARCHAR(127) NOT NULL,
    v2 VARCHAR(127) NOT NULL,
    vm VARCHAR(32512) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=GBK

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

-- 測試邊界值32511，建立成功，說明兩個長度標識位共佔去了2位元組
CREATE TABLE test_varchar_length4(
    v1 VARCHAR(127) NOT NULL,
    v2 VARCHAR(127) NOT NULL,
    vm VARCHAR(32511) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=GBK
> OK

接下來將兩個欄位調大到128字元，每個列可能的最大位元組數為256、理論上長度標識位是2位元組。那麼還可以新增最大65535 - 128*2*2（2個列，每個列最大佔127*2個位元組）- 2*2（2個長度標識位） = 65017位元組的非空VARCHAR列，約等於32508個字元：

-- 測試邊界值32509，確認仍然過大
CREATE TABLE test_varchar_length5(
    v1 VARCHAR(128) NOT NULL,
    v2 VARCHAR(128) NOT NULL,
    vm VARCHAR(32509)
)ENGINE=INNODB DEFAULT CHARSET=GBK

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

-- 測試邊界值32508，建立成功，說明兩個長度標識位共佔去了4位元組
CREATE TABLE test_varchar_length5(
    v1 VARCHAR(128) NOT NULL,
    v2 VARCHAR(128) NOT NULL,
    vm VARCHAR(32508)
)ENGINE=INNODB DEFAULT CHARSET=GBK

> OK

小結

• UTF8MB4字元中，中文字元需要3個位元組（大部分中文只需要3位元組，4位元組主要是emoji等輔助平面字元），那麼“中國cn”需要3+3+1+1共 8個位元組
• VARCHAR(64) CHARSET utf8mb4欄位，資料最大可能的位元組數是64*4=256，所以需要 2個位元組 作為長度標識位；
• 該欄位是可以為空的，那麼還需要NULL標識位，MySQL會生成一個 1位元組 的NULL標識列來記錄；
• 所以要儲存“中國cn”，列需要8 + 2個位元組，還需要1位元組作為NULL標識列；因為該列是多個列共享的，如果該表只有一個欄位，那麼可以儲存開銷應該是11個位元組，否則只能算作10.125位元組（1/8等於0.125）

所以MySQL要儲存”中國cn”需要的是10.125或11位元組。

回到開始的問題，i的最大字元數為216-1（varchar的最大位元組長度）- 2X9（9列的長度識別符號消耗的位元組數）-9/8（9列允許空，1列NULL就用 1 bit 標識，那麼就是9 bit，8bit=1byte）-8X7279 = 7283，所以i列最大能容納的字元數在這裡為7283

本作品採用《CC 協議》，轉載必須註明作者和本文連結