char和varchar有哪些區(qū)別？varchar最大長度是多少？

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

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

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

那你猜一猜，MySQL存儲時用了多少字節(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。

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

• VARCHAR的定義

• VARCHAR的最大長度

1. 最大行大小

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

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

4. VARCHAR的長度標(biāo)識位

5. 樣例

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

??

VARCHAR是變長字符串。

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

為了便于理解，我用CHAR定長類型來對比介紹。先看兩個小例子：

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

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

概括地說，VARCHAR和CHAR都是MySQL的字符串類型，存儲多個字符、可設(shè)置最大存儲的字符數(shù)，存儲開銷都與數(shù)據(jù)長度、字符集有關(guān)。是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

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

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

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

• 存儲字符的字符集

算法如下：

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

下面通過逐步實例驗證，演示如何計算出最大長度。

最大行大小

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

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

前面講過，VARCHAR聲明的長度是指字符數(shù)。要換算為65536字節(jié)，最好一個字符只占一個字節(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

可以看到報錯了，提示我們行最大長度為65535字節(jié)。

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

/** 測試邊界值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

/** 測試邊界值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，每個字符只占用1個字節(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)識位

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

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

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

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

Talk is cheep，一起來驗證下：

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

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

/** 測試邊界值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

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

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

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

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

• GBK：單字符最大可占用2個字節(jié)。

• UTF8：單字符最大可占用3個字節(jié)。

• UTF8MB4：單字符最大占4個字節(jié)。

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

VARCHAR的長度標(biāo)識位

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

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

存儲開銷是小于255只要1字節(jié)、大于255后使用兩字節(jié)。是因為按照可能的數(shù)據(jù)大小，分為0 - 255(28)、256 - 65535(216)，剛好對應(yīng)1字節(jié)和2字節(jié)。

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

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

以VARCHAR(1)為例，可以存1個字符，MySQL會額外找一個字節(jié)存放長度標(biāo)識

樣例

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

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

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

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

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

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

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

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

mysql> drop table test_varchar_length;
Query OK, 0?rows affected?(0.01?sec)
/** 測試邊界值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

/** 測試邊界值32511，創(chuàng)建成功，說明兩個長度標(biāo)識位共占去了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)

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

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

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

mysql> drop table test_varchar_length;
Query OK, 0?rows affected?(0.01?sec)
/** 測試邊界值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

/** 測試邊界值32508，創(chuàng)建成功，說明兩個長度標(biāo)識位共占去了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īng)得到了提升。

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

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

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

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

• 所以要存儲“中國cn”，列需要8 + 2個字節(jié)，還需要1字節(jié)作為NULL標(biāo)識列；因為該列是多個列共享的，如果該表只有一個字段，那么可以存儲開銷應(yīng)該是11個字節(jié)，否則只能算作10.125字節(jié)（1/8等于0.125）

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

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