char和varchar在MySQL中的区别

原文地址: CoderGO : char和varchar在MySQL中的区别
注：存储引擎为InnoDB

1.varchar

可变长字符串。

特点：

1. 存储空间不固定，根据字段长度决定。
2. 需要额外的一个或者两个字节记录字符串的长度，当列长度小于255字节，使用一个字节，否则使用两个。
3. 如果列可以为null，则需要额外的一个字节作为标志。
4. 最大长度为65535字节，65535字节是一行数据中，所有varchar字段字节的长度之和（包括长度记录位和null标志位[如果需要的话]），注意：最大长度单位是字节而非字符（字符类型为GBK或UTF-8而非latin1情况是不同的）。

缺点：

• 每个列需要额外的一到两个字节存储最大长度。
• 行边长，update时可能会增加行的长度，如果一个页内存储空间不够，会导致页分裂。

适用：

• 字符串列的最大长度比平均长度大很多，列更新少。
• 使用了像UTF-8这样复杂的字符集，每个字符都是用不同的字节数存储。

存储空间测试如下：

1.varchar_col实际长度为：65535+2[长度记录：2B]+1[null标志位：1B] = 65538，表创建失败。

mysql> create table varchar_test(varchar_col varchar(65535));
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

2.varchar_col实际长度为：65533+2[长度记录：2B]+1[null标志位：1B] = 65536，表创建失败。

mysql> create table varchar_test(varchar_col 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

3.varchar_col实际长度为：65533+2[长度记录：2B] = 65535，表创建成功。

mysql> create table varchar_test(varchar_col varchar(65533) not null);
Query OK, 0 rows affected (0.12 sec)

4.varchar_col实际长度为：65530+2[长度记录：2B] + 2 + [长度记录：1B] + [null标志位：1B]= 65536，表创建失败。

mysql> create table varchar_test(varchar_col varchar(65530) not null, tt varchar(2));
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

5.varchar_col实际长度为：60000+2[长度记录：2B] + 5331 + [长度记录：2B] = 65535，表创建成功。

mysql> create table varchar_test(varchar_col varchar(60000) not null, tt varchar(5531) not null);
Query OK, 0 rows affected (0.14 sec)

6.varchar_col实际长度为：60000+2[长度记录：2B] + 5331 + [长度记录：2B] + null标志位：1B] = 65536，表创建失败。

mysql> create table varchar_test(varchar_col varchar(60000) not null, tt varchar(5531));
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

2.char

定长字符串。

特点：
1. 存储所需空间固定。
2. 长度不够时内部存储使用空格填充。
3. 若字段末尾存在空格，检索出来自动截断末尾空格（因为分不清空格是字段含有的还是1中填充产生的,varchar不存在此情况）。

适用：

• 适合存储很短的或者长度接近同一个长度的字符串。
• 对于经常更新的字段，定长的char不容易导致碎片。

空格截断测试如下：

1.创建表

mysql> create table char_test(char_col char(10));
Query OK, 0 rows affected (0.55 sec)

2.插入数据

mysql> insert into char_test(char_col) values
    -> ('string1'), (' string2'), ('string3 ');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

3.检索

mysql> select concat("'", char_col, "'") from char_test;
+----------------------------+
| concat("'", char_col, "'") |
+----------------------------+
| 'string1'                  |
| ' string2'                 |
| 'string3'                  |
+----------------------------+

可以看出插入’string3 ‘有空格，检索出来空格被截断。

参考：

1.高性能MySQL
2.MySQL技术内幕：InnoDB存储引擎