MySQL字元資料型別char與varchar的區別

資料型別差不多是接觸mysql一開始就瞭解的內容，最近遇到幾個現象如varchar自動轉mediumtext，blob儲存效能的問題，不得不回頭明確一下關於MySQL常用資料型別的選擇。

mysql手冊這裡 已經講的很清楚了。它們都是定義字串型欄位時常用的型別，但它們儲存和檢索的方式有不同，最大長度和尾部的空格是否保留也有差別。

char型別是使用固定長度空間進行儲存，範圍0-255。比如CHAR(30)能放30個位元組，存放abcd時，尾部會以空格補齊，實際佔用空間 30bytes 。檢索它的時候尾部空格會被去除。

char善於儲存經常改變的值，或者長度相對固定的值，比如type、ip地址或md5之類的資料，不容易產生碎片。關於它的效率可以參考這裡。

varchar型別儲存可變長度字串，範圍0-65535（但受到單行最大64kb的限制）。比如用varchar(30)去存放abcd，實際使用5個位元組，因為還需要使用額外1個位元組來標識字串長度（0-255使用1個位元組，超過255需要2個位元組）。

varchar善於儲存值的長短不一的列，也是用的最多的一種型別，節省磁碟空間。update時varchar列時，如果新資料比原資料大，資料庫需要重新開闢空間，這一點會有效能略有損耗，但innodb引擎下查詢效率比char高一點。這也是innodb官方推薦的型別。

如果儲存時真實長度超過了char或者varchar定義的最大長度呢？

• 在SQL嚴格模式下，無論char還是varchar，如果尾部要被截斷的是非空格，會提示錯誤，即插入失敗

• 在SQL非嚴格模式下，無論char還是varchar，如果尾部要被截斷的是非空格，會提示warning，但可以成功

• 如果尾部要被截斷的是空格，無論SQL所處模式，varchar都可以插入成功但提示warning；char也可以插入成功，並且無任何提示

這裡特意提到SQL的嚴格模式，是因為在工作中也遇到過一些坑，參考[MySQL的sql_mode嚴格模式注意點]()。

貼上官方的一個表格：

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
“	` `	4 bytes	“	1 byte
`ab`	`ab `	4 bytes	`ab`	3 bytes
`abcd`	`abcd`	4 bytes	`abcd`	5 bytes
`abcdefgh`	`abcd`	4 bytes	`abcd`	5 bytes

另外，mysql欄位值比較時預設是不區分大小寫的，這是由於他們的校對規則（一般是 utf8_general_ci）決定的，按字元比較，所以查詢時 值尾部 的空格也是被忽略的，除非建表時對列指定 BINARY （校對字符集變成utf8_bin）或者select * from vc where binary v=`ab `;，就會按位元組比較，即比較時區分大小寫和尾部空格。

需要注意的是，使用varchar不能因為長度可變就隨意分大空間，比如90個位元組能放夠的列定義成varchar(200)，因為開闢記憶體時是以200位元組進行的，遇到需要filesort或tmp table作業可能會帶來不利影響。

最後研究一下字符集對儲存長度影響，以 create table tc_utf8(c1 int primary key auto_increment, c2 char(30), c3 varchar(N)) charset=utf8; 為例：

字符集為utf8，於是中文每個字元佔3個位元組，英文還是1個位元組，所以N最大為 (65535-1-2-4-303)/3 = 21812，即最多能存放21812個英文、數字、漢字。其中65535是單行最大限制，減1是NULL標識位，減2的是頭部的2個位元組標識長度，減303的原因是char(30)佔用90個位元組，最後除以3還是因為utf8最長用3個位元組表示一個字元。

但有人會說，utf8的英文字元只需要1個位元組表示，並不佔用3個位元組，在存ASCII字元的情況下N是不是可以更大呢。答案是否定的，因為定義表的時候mysql事先並不知道c3要存的是英文還在中文，只能以最大來計。mysql也是以這種方式來確保行最大65535bytes限制：資料行只要出現一個ascii字元（如英文字母、數字），就永遠達不到65535，資料行全中文則剛好滿。

還有一種特殊情況：

mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.12 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 rows in set (0.13 sec)

mysql> create table tc_utf8_21812(c1 int primary key auto_increment, c2 char(30), c3 varchar(21812)) charset=utf8;
Query OK, 0 rows affected (0.10 sec)

mysql> create table tc_utf8_21813(c1 int primary key auto_increment, c2 char(30), c3 varchar(21845)) charset=utf8;
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 tc_utf8_21846(c1 int primary key auto_increment, c2 char(30), c3 varchar(21846)) charset=utf8;
Query OK, 0 rows affected, 1 warnings (0.10 sec)

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1246 | Converting column `c3` from VARCHAR to TEXT |
+-------+------+---------------------------------------------+
1 rows in set (0.14 sec)

即在非嚴格模式下，因為N=21813 > 21812，所以報 Row size too large 錯誤。但N=21846 > (65535/3)時，只是出現warnings，varchar自動變成了mediumtext 型別。

細心的朋友可能注意到上面開始我看了一下字符集 show variabels like "char%";，因為接下來要說明另外一個問題：客戶端字符集與database不一樣的情況。

我們回到 N<=21812 的正常情況：

CREATE TABLE `tc_utf8` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(30) DEFAULT NULL,
  `c3` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入一些資料：

mysql> set names utf8;
mysql> insert into tc_utf8(c2,c3) values(`en_30`,repeat(`a`,30));
Query OK, 1 rows affected (17.87 sec)

mysql> insert into tc_utf8(c2,c3) values(`en_31`,repeat(`b`,31));
Query OK, 1 rows affected, 1 warnings (0.10 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column `c3` at row 1 |
+---------+------+-----------------------------------------+
1 rows in set (0.14 sec)

mysql> insert into tc_utf8(c2,c3) values(`zh_30`,repeat(`中`,30));
Query OK, 1 rows affected (0.18 sec)

mysql> insert into tc_utf8(c2,c3) values(`zh_31`,repeat(`文`,31));
Query OK, 1 rows affected, 1 warnings (0.09 sec)

意料之中，漢字同樣被截斷

ysql> select c2,c3,length(c3),char_length(c3) from tc_utf8;
+-------+------------+-----------------+------------------------------------------------------------------------+
| c2    | length(c3) | char_length(c3) | c3                                                                     |
+-------+------------+-----------------+------------------------------------------------------------------------+
| en_30 |         30 |              30 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa                                         |
| en_31 |         30 |              30 | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb                                         |
| zh_30 |         90 |              30 | 中中中中中中中中中中中中中中中中中中中中中中中中中中中中中中 |
| zh_31 |         90 |              30 | 文文文文文文文文文文文文文文文文文文文文文文文文文文文文文文 |
+-------+------------+-----------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

上面的en_30代表insert的時候存入30個英文字元。可以看到30個a佔用30個位元組，30個漢字佔用90個位元組，大於30的會被截斷，證實了文章一開頭的說法。

mysql> set names latin1;

mysql> insert into tc_utf8(c2,c3) values(`zh_30_latin1`,repeat(`中`,30));
Query OK, 1 rows affected, 1 warnings (0.10 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column `c3` at row 1 |
+---------+------+-----------------------------------------+
1 rows in set (0.14 sec)

mysql> insert into tc_utf8(c2,c3) values(`zh_10_latin1`,repeat(`中`,10));
Query OK, 1 rows affected (0.10 sec)

mysql> insert into tc_utf8(c2,c3) values(`zh_10_latin1`,repeat(`文`,10));
Query OK, 1 rows affected (0.11 sec)

mysql> insert into tc_utf8(c2,c3) values(`zh_11_latin1`,repeat(`文`,11));
Query OK, 1 rows affected, 1 warnings (0.12 sec)

截斷

上面的實驗顯示，db table是utf8，但客戶端連線時使用latin1，在非嚴格模式下 varchar(30) 只能存10個漢字，多餘的尾部被截斷了

我們來看一下佔用位元組的情況：（2,3行的亂碼是意料之中的）

mysql> select c1,c2,c3,length(c3),char_length(c3) from tc_utf8;
+----+--------------+--------------------------------+------------+-----------------+
| c1 | c2           | c3                             | length(c3) | char_length(c3) |
+----+--------------+--------------------------------+------------+-----------------+
| 1  | en_30        | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | 30         | 30              |
| 2  | en_31        | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb | 30         | 30              |
| 3  | zh_30        | ?????????????????????????????? | 90         | 30              |
| 4  | zh_31        | ?????????????????????????????? | 90         | 30              |
| 5  | zh_30_latin1 | 中中中中中中中中中中 | 60         | 30              |
| 6  | zh_10_latin1 | 中中中中中中中中中中 | 60         | 30              |
| 7  | zh_10_latin1 | 文文文文文文文文文文 | 80         | 30              |
| 9  | zh_11_latin1 | 文文文文文文文文文文 | 80         | 30              |
+----+--------------+--------------------------------+------------+-----------------+
8 rows in set (0.14 sec)

看到char_length函式算出的中、英文字元個數都是30，但一個“中”佔6位元組，一個“文”佔8位元組，是不是很詫異，這中間有數次的編碼轉換過程，有興趣 可以參考 http://mysql.rjweb.org/doc.php/charcoll ，是可以模擬出來的。

在嚴格模式下就沒這麼複雜了，所以儘量使用 STRICT_TRANS_TABLES ，避免意外的情況帶入生產環境。早期設計的時候就要保持客戶端與資料庫字符集一致。