以我多年經(jīng)驗(yàn)來看，VARCHAR的最大長(zhǎng)度、字符串類型選擇，用MySQL的人中十之七八是不清楚的。

本文不止介紹了原理，還提供了案例手把手教你自己分析，徹底解決你的疑惑。

假設(shè)有個(gè)VARCHAR(64) CHARSET utf8mb4列，存儲(chǔ)了中國(guó)cn這個(gè)字符串。

那你猜一猜，MySQL存儲(chǔ)時(shí)用了多少字節(jié)？

A：4 Bytes

B：5 Bytes

C：8 Bytes

D：9 Bytes

E：10 Bytes

F：10.125 Bytes

G：11 Bytes

H：12 Bytes

I：12.125 Bytes

K：13 Bytes

正確答案是F和G。

如果您沒猜對(duì)，那么花7~10分鐘讀完本文，即可破解這一謎題。成長(zhǎng)快樂輕輕松松。

• VARCHAR的定義

• VARCHAR的最大長(zhǎng)度

1. 最大行大小

2. 可空列標(biāo)識(shí)位

3. 字符集的單字符最大字節(jié)數(shù)

4. VARCHAR的長(zhǎng)度標(biāo)識(shí)位

5. 樣例

本文內(nèi)容適用于MySQL 5.5/5.6/5.7/8.x

??

VARCHAR是變長(zhǎng)字符串。

考慮其變長(zhǎng)原理中有較多要素，在具體分解前，有必要一起重溫下官方定義。

為了便于理解，我用CHAR定長(zhǎng)類型來對(duì)比介紹。先看兩個(gè)小例子：

• VARCHAR(4)，最多存儲(chǔ)4個(gè)字符，有幾個(gè)字符存儲(chǔ)幾個(gè)。存儲(chǔ)字節(jié)數(shù) = 數(shù)據(jù)值的字節(jié)和 + 1字節(jié)（長(zhǎng)度標(biāo)識(shí)，后面會(huì)講到）

• CHAR(4)，最多存儲(chǔ)4個(gè)字符，不足4個(gè)尾部用空格填滿。存儲(chǔ)字節(jié)數(shù) = 數(shù)據(jù)值的字節(jié)和 + 補(bǔ)位空格數(shù)

概括地說，VARCHAR和CHAR都是MySQL的字符串類型，存儲(chǔ)多個(gè)字符、可設(shè)置最大存儲(chǔ)的字符數(shù)，存儲(chǔ)開銷都與數(shù)據(jù)長(zhǎng)度、字符集有關(guān)。是MySQL最常用的字符串類型。

CHAR和VARCHAR具體對(duì)比：

如果開啟PAD_CHAR_TO_FULL_LENGTH模式，檢索時(shí)尾部空格不會(huì)去除

CHAR超過255字符會(huì)報(bào)錯(cuò)，提示使用TEXT或BLOB：

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

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

• 行存儲(chǔ)的最大字節(jié)數(shù)

• 數(shù)據(jù)之外的存儲(chǔ)開銷，官方定義中包括：NULL標(biāo)識(shí)、長(zhǎng)度標(biāo)識(shí)

• 存儲(chǔ)字符的字符集

算法如下：

最大長(zhǎng)度(字符數(shù)) = （行存儲(chǔ)最大字節(jié)數(shù) - NULL標(biāo)識(shí)列占用字節(jié)數(shù) - 長(zhǎng)度標(biāo)識(shí)字節(jié)數(shù)） / 字符集單字符最大字節(jié)數(shù)。有余數(shù)時(shí)向下取整。

下面通過逐步實(shí)例驗(yàn)證，演示如何計(jì)算出最大長(zhǎng)度。

最大行大小

MySQL行默認(rèn)最大65535字節(jié)，是所有列共享的，所以VARCHAR的最大值受此限制。

接下來，我們要?jiǎng)?chuàng)建一個(gè)65536字節(jié)的VARCHAR，來驗(yàn)證這個(gè)邊界值。

前面講過，VARCHAR聲明的長(zhǎng)度是指字符數(shù)。要換算為65536字節(jié)，最好一個(gè)字符只占一個(gè)字節(jié)。

所以這里使用了latin1字符集（MySQL默認(rèn)字符集，不指定即為默認(rèn)）。

mysql> create table test_varchar_length(v varchar(65536) not?null);

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

可以看到報(bào)錯(cuò)了，提示我們行最大長(zhǎng)度為65535字節(jié)。

如果我們要插入一個(gè)非空的VARCHAR，其最大長(zhǎng)度不能超過65535(行最大值) - 2(長(zhǎng)度標(biāo)識(shí)位) = 65533字節(jié)（長(zhǎng)度標(biāo)識(shí)位需2字節(jié)才能表示2^16=65536個(gè)數(shù)字）：

/** 測(cè)試邊界值65534，確認(rèn)仍然過大；注意這里使用默認(rèn)字符集latin1、單字節(jié)字符集 */
mysql> create table test_varchar_length(v varchar(65534) not null); 
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

/** 測(cè)試邊界值65533，創(chuàng)建成功，說明行最大值為65535 */
mysql> create table test_varchar_length(v varchar(65533) not null); 
Query OK, 0?rows affected?(0.02?sec)

/** 查看默認(rèn)字符集，確認(rèn)是latin1，每個(gè)字符只占用1個(gè)字節(jié) */
mysql> show create table test_varchar_length;
+----------------------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------+------------------------------------------------------------------------------------------------------------+
| test_varchar_length | CREATE TABLE `test_varchar_length` (
??`v` varchar(65533) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------------------+------------------------------------------------------------------------------------------------------------+
1?row in?set?(0.00?sec)

可空列標(biāo)識(shí)位

在COMPACT、DYNAMIC行格式下，行大小除了數(shù)據(jù)列長(zhǎng)度，還包括可空列標(biāo)識(shí)，即NULL標(biāo)識(shí)位。

如果有一個(gè)列允許為空，則需要1 bit來標(biāo)識(shí)，每8 bits的標(biāo)識(shí)會(huì)組成一個(gè)字段，該字段會(huì)存放在每行最開始的位置。

注意，這個(gè)標(biāo)識(shí)位不是放在每列，而是每行共享。

假設(shè)一張表中存在N個(gè)可空字段，NULL標(biāo)識(shí)位需要?N / 8 ? （向上取整）個(gè)字節(jié)。此時(shí)整行可用于數(shù)據(jù)存儲(chǔ)的空間只有65535 ? ? N / 8 ?個(gè)字節(jié)。

Talk is cheep，一起來驗(yàn)證下：

在行大小的例子中，我們知道最大可創(chuàng)建65533字節(jié)長(zhǎng)度的非空VARCHAR列。現(xiàn)在要?jiǎng)?chuàng)建一個(gè)可空列，每行需要1 bit的NULL標(biāo)識(shí)位、MySQL會(huì)將其組裝成1 byte的字段存放，那么我們應(yīng)該可創(chuàng)建最大為65533（最大非空VARCHAR列） - 1（NULL標(biāo)識(shí)列）= 65532字節(jié)的可空VARCHAR列：

/** 刪除前面創(chuàng)建的表 */
mysql> drop?table?test_varchar_length;
Query?OK, 0?rows?affected?(0.01?sec)

/** 測(cè)試邊界值65533，確認(rèn)仍然過大；注意這里使用默認(rèn)字符集latin1、單字節(jié)字符集 */
mysql> create?table?test_varchar_length(v varchar(65533));
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

/** 測(cè)試邊界值65532，創(chuàng)建成功，說明可空標(biāo)識(shí)列確實(shí)占去了1字節(jié)；注意這里使用默認(rèn)字符集latin1、單字節(jié)字符集 */
mysql> create?table?test_varchar_length(v varchar(65532));
Query?OK, 0?rows?affected?(0.03?sec)

計(jì)算VARCHAR的最大長(zhǎng)度，可空標(biāo)識(shí)位是最容易忽略的。

字符集的單字符最大字節(jié)數(shù)

字符集單字符最大字節(jié)數(shù)不難理解，列舉MySQL常見的三個(gè)字符集：

• GBK：?jiǎn)巫址畲罂烧加?個(gè)字節(jié)。

• UTF8：?jiǎn)巫址畲罂烧加?個(gè)字節(jié)。

• UTF8MB4：?jiǎn)巫址畲笳?個(gè)字節(jié)。

假設(shè)還有6字節(jié)可以存放字符，按單字符占用最大字節(jié)數(shù)來算，可以存放3個(gè)GBK、2個(gè)UTF8、1個(gè)UTF8MB4。

VARCHAR的長(zhǎng)度標(biāo)識(shí)位

長(zhǎng)度標(biāo)識(shí)位是相對(duì)比較復(fù)雜的，網(wǎng)上的介紹錯(cuò)的很多，也容易算錯(cuò)。

其作用是記錄數(shù)據(jù)的字節(jié)數(shù)。

存儲(chǔ)開銷是小于255只要1字節(jié)、大于255后使用兩字節(jié)。是因?yàn)榘凑湛赡艿臄?shù)據(jù)大小，分為0 - 255(28)、256 - 65535(216)，剛好對(duì)應(yīng)1字節(jié)和2字節(jié)。

但要注意，其計(jì)算根據(jù)的是字段聲明的字符長(zhǎng)度、計(jì)算可能的字節(jié)數(shù)，再?zèng)Q定長(zhǎng)度標(biāo)志的字節(jié)數(shù)。如VARCHAR(100)，字符集為UTF8，可能的字節(jié)數(shù)為300，長(zhǎng)度標(biāo)識(shí)則為2字節(jié)。這是網(wǎng)上介紹錯(cuò)的最多的。

另外長(zhǎng)度標(biāo)志位是底層存儲(chǔ)開銷，不占用字段聲明的字符長(zhǎng)度。聲明的字符長(zhǎng)度的是數(shù)據(jù)的字符數(shù)，數(shù)據(jù)的字節(jié)數(shù)與字符集有關(guān)。

以VARCHAR(1)為例，可以存1個(gè)字符，MySQL會(huì)額外找一個(gè)字節(jié)存放長(zhǎng)度標(biāo)識(shí)

樣例

公式應(yīng)該都理解了：VARCHAR的最大長(zhǎng)度 = （最大行大小 - NULL標(biāo)識(shí)列占用字節(jié)數(shù) - 長(zhǎng)度標(biāo)識(shí)字節(jié)數(shù)） / 字符集單字符最大字節(jié)數(shù)。有余數(shù)時(shí)向下取整。

接下來通過實(shí)驗(yàn)來驗(yàn)證。為了便于理解計(jì)算，例子做了一些調(diào)整：

• 不設(shè)置可空列、這樣可以去掉NULL標(biāo)識(shí)列

• 為了便于體現(xiàn)長(zhǎng)度標(biāo)識(shí)位的差距，采用多個(gè)列的形式放大其存在

• 為了體現(xiàn)按可能字節(jié)數(shù)計(jì)算長(zhǎng)度，這里采用多字節(jié)的字符集GBK

創(chuàng)建一個(gè)表，包含2個(gè)非空VARCHAR(127)，每個(gè)列存儲(chǔ)開銷為127*2(可能的最大字節(jié)數(shù), GBK字符占2字節(jié))+長(zhǎng)度標(biāo)識(shí)位1=255字節(jié)：

• 剩余空間為65535 - 255*2 = 65025字節(jié)

• 剩余空間可存放一個(gè)VARCHAR(32511) NOT NULL列（32511*2(GBK字符占2字節(jié))+2(長(zhǎng)度標(biāo)識(shí)位占2字節(jié))=65024）

mysql> drop table test_varchar_length;
Query OK, 0?rows affected?(0.01?sec)
/** 測(cè)試邊界值32512，確認(rèn)仍然過大 */
mysql> create table test_varchar_length(v1 varchar(127) not null,v2 varchar(127) not null,vm varchar(32512) not null) CHARSET=GBK; 
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

/** 測(cè)試邊界值32511，創(chuàng)建成功，說明兩個(gè)長(zhǎng)度標(biāo)識(shí)位共占去了2字節(jié) */
mysql> create table test_varchar_length(v1 varchar(127) not null,v2 varchar(127) not null,vm varchar(32511) not null) CHARSET=GBK; 
Query OK, 0?rows affected?(0.02?sec)

接下來將兩個(gè)字段調(diào)大到128字符，每個(gè)列的存儲(chǔ)為最大字節(jié)數(shù)256+長(zhǎng)度標(biāo)識(shí)位2=258字節(jié)

• 剩余空間65535 - 258*2 = 65019字節(jié)

• 剩余空間可存放一個(gè)VARCHAR(32508) NOT NULL列（32508*2(GBK字符占2字節(jié))+2(長(zhǎng)度標(biāo)識(shí)位占2字節(jié))=65018）：

mysql> drop table test_varchar_length;
Query OK, 0?rows affected?(0.01?sec)
/** 測(cè)試邊界值32509，確認(rèn)仍然過大 */
mysql> create table test_varchar_length(v1 varchar(128) not null,v2 varchar(128) not null,vm varchar(32509) not null) CHARSET=GBK; 
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

/** 測(cè)試邊界值32508，創(chuàng)建成功，說明兩個(gè)長(zhǎng)度標(biāo)識(shí)位共占去了4字節(jié) */
mysql> create table test_varchar_length(v1 varchar(128) not null,v2 varchar(128) not null,vm varchar(32508) not null) CHARSET=GBK; 
Query OK, 0?rows affected?(0.02?sec)

恭喜你，能看到這里的人估計(jì)不多，堅(jiān)持下來的你已經(jīng)得到了提升。

那么再一起解下最初的問題：

• UTF8MB4字符中，中文字符需要3個(gè)字節(jié)（大部分中文只需要3字節(jié)，4字節(jié)主要是emoji等輔助平面字符），那么“中國(guó)cn”需要3+3+1+1共 8個(gè)字節(jié)

• VARCHAR(64) CHARSET utf8mb4字段，數(shù)據(jù)最大可能的字節(jié)數(shù)是64*4=256，所以需要 2個(gè)字節(jié) 作為長(zhǎng)度標(biāo)識(shí)位；

• 該字段是可以為空的，那么還需要NULL標(biāo)識(shí)位，MySQL會(huì)生成一個(gè) 1字節(jié) 的NULL標(biāo)識(shí)列來記錄；

• 所以要存儲(chǔ)“中國(guó)cn”，列需要8 + 2個(gè)字節(jié)，還需要1字節(jié)作為NULL標(biāo)識(shí)列；因?yàn)樵摿惺嵌鄠€(gè)列共享的，如果該表只有一個(gè)字段，那么可以存儲(chǔ)開銷應(yīng)該是11個(gè)字節(jié)，否則只能算作10.125字節(jié)（1/8等于0.125）

所以答案是10.125或11字節(jié)。

來源：learn.blog.csdn.net/article/details/103341778