Mysql中char和varchar的区别

前言

这个标题起的过于俗套，前一段时间我还写了一篇总结《Mysql5.7版本中数据表字段可用的类型》来批判这种对比，原因是对比时没有指明数据库，内容写的是char、varchar和nvarchar的对比，结果我测试了半天发现Mysql当前版本根本没有nvarchar，浪费来了不少时间。

问题起因

真香定律来的总是这么快，这才过了几天，我也来写写Mysql中char和varchar究竟有什么区别，起因是看到CSDN好友“铁柱同学”一篇关于innodb主键长度最大为767字节的讲解，里面涉及到一个char类型最大存储255个字符，按照utf8编码来看最大的字节数应该是255*3=765个字节的知识点。现在来看767的来源好像并不是256*3-1，而是255*3+2，这个2就是存储char类型字段中实际有多少个字节的。

有点跑题了，实际上是在研究索引长度的过程中，我发现我对char和varchar这两个类型一直存在着误解，因为一直做游戏开发的缘故，游戏数据的存储一般使用varbinary来存，导致我把字符和字节有点弄混了，所以我一直认为在utf8编码下char(9)可以存储9个英文字符，或者3个中文汉字，实际我做完实验后发现char(9)也可以正常存储9个汉字。

提到字符和字节，初学者可能会有点蒙，实际上它们两者之间是需要通过编码来转换的，之前做过游戏的多语言版本，所以对这一块还是比较熟的，字节就是计算机中的8个二进制位，而字符是每个语言中的不可分割的单元，字符转换成字节需要依赖编码，实际上编码就是一本大字典，里面对应了每个字符在当前编码下转换成字节是什么样的，ANSI是一本字典，UTF8也是一本字典，编码的类型还有很多，每种编码都记录了各自的转换结果。

举个例子，“中”这个字是汉字中的一个字符，在ANSI这本字典中对应的是2个字节，而在UTF8这本字典中对应的是3个字节，而C这个字母是英文中的一个字符，在ANSI这本字典中对应的是1个字节，而在UTF8这本字典中对应的同样是1个字节，从这个例子中可以简单理解下字节与字符的关系。

多说一句，不要认为UTF8编码中汉字转换成字节都是3个字节，实际情况是常用字一般都占用了3个字节，但是中国语言博大精深，光语言平面就单独占了好几个，有些汉字转换成UTF8编码可能需要4个字节，5个字节甚至是6个字节，这一点不要形成思维定式，认为汉字在UTF8编码下都是3个字节。

length 和 char_length

今天之前我是不知道Mysql还有一个char_length函数的，发现这个函数后越发感觉Mysql的强大，这两个函数的区别就是length用来统计字段中的字节数，char_length用来统计字段中的字符数，接下来我们用一个例子来看看这两个函数以及char、varchar的区别。

测试环境

Windows 10
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

测试过程

1. 首先创建一个带有char和varchar类型的测试表，查看表结构发现编码为utf8

   mysql> create table diff(id int, s1 char(10), s2 varchar(10));
   Query OK, 0 rows affected (0.07 sec)
   
   mysql> show create table diff;
   +-------+------------------------------------------------------------------------+
   | Table | Create Table                                                           |
   +-------+------------------------------------------------------------------------+
   | diff  | CREATE TABLE `diff` (
     `id` int(11) DEFAULT NULL,
     `s1` char(10) DEFAULT NULL,
     `s2` varchar(10) DEFAULT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
   +-------+------------------------------------------------------------------------+
   1 row in set (0.07 sec)

2. 插入少于10个字符的测试数据，然后查看结果，发现字节数为10，字符数为6

   mysql> insert into diff values(1, "测试test", "测试test");
   Query OK, 1 row affected (0.01 sec)
   
   mysql> select id, s1, length(s1), char_length(s1), s2, length(s2), char_length(s2) from diff;
   +----+----------+------------+-----------------+----------+------------+-----------------+
   | id | s1       | length(s1) | char_length(s1) | s2       | length(s2) | char_length(s2) |
   +----+----------+------------+-----------------+----------+------------+-----------------+
   |  1 | 测试test |         10 |               6 | 测试test |         10 |               6 |
   +----+----------+------------+-----------------+----------+------------+-----------------+
   1 row in set (0.06 sec)

3. 增加插入长度后发现，可以超过10个字节，可以完整存储10个汉字

   mysql> insert into diff values(2, "测试策划和开发做游戏", "测试策划和开发做游戏");
   Query OK, 1 row affected (0.01 sec)
   
   mysql> select id, s1, length(s1), char_length(s1), s2, length(s2), char_length(s2) from diff;
   +----+----------------------+------------+-----------------+----------------------+------------+-----------------+
   | id | s1                   | length(s1) | char_length(s1) | s2                   | length(s2) | char_length(s2) |
   +----+----------------------+------------+-----------------+----------------------+------------+-----------------+
   |  1 | 测试test             |         10 |               6 | 测试test             |         10 |               6 |
   |  2 | 测试策划和开发做游戏 |         30 |              10 | 测试策划和开发做游戏 |         30 |              10 |
   +----+----------------------+------------+-----------------+----------------------+------------+-----------------+
   2 rows in set (0.10 sec)

4. 分别增加s1和s2字段长度后发现，均无法正常插入，Mysql给出报错信息

   mysql> insert into diff values(2, "测试策划和开发做游戏OK", "测试策划和开发做游戏");
   1406 - Data too long for column 's1' at row 1
   mysql> insert into diff values(2, "测试策划和开发做游戏", "测试策划和开发做游戏OK");
   1406 - Data too long for column 's2' at row 1
   mysql>

5. 至此没有看出区别，在插入内容前后都加上空格测试一下

   mysql> select id, s1, concat('#', s1, '$'), length(s1) as len_s1, char_length(s1) as clen_s1,
                   s2, concat('#', s2, '$'), length(s2) as len_s2, char_length(s2) as clen_s2 from diff;
   +----+----------------------+------------------------+--------+---------+----------------------+------------------------+--------+---------+
   | id | s1                   | concat('#', s1, '$')   | len_s1 | clen_s1 | s2                   | concat('#', s2, '$')   | len_s2 | clen_s2 |
   +----+----------------------+------------------------+--------+---------+----------------------+------------------------+--------+---------+
   |  1 | 测试test             | #测试test$             |     10 |       6 | 测试test             | #测试test$             |     10 |       6 |
   |  2 | 测试策划和开发做游戏 | #测试策划和开发做游戏$ |     30 |      10 | 测试策划和开发做游戏 | #测试策划和开发做游戏$ |     30 |      10 |
   |  3 |  OK                  | # OK$                  |      3 |       3 |  OK                  | # OK $                 |      4 |       4 |
   +----+----------------------+------------------------+--------+---------+----------------------+------------------------+--------+---------+

这一次出现了区别，char类型的字段去掉了尾部的空格，而varcahr了类型的字段原样存储，没有去掉尾部空格，两者对于头部的空格都是存储的，这导致两者显示的字节数和字符数都不相同了。

1. 分别使用不带空格、带头部空格，头尾都带空格进行测试

   mysql> select * from diff where s1 = 'OK ';
   Empty set
   
   mysql> select * from diff where s2 = 'OK ';
   Empty set
   
   mysql> select * from diff where s1 = ' OK ';
   +----+-----+------+
   | id | s1  | s2   |
   +----+-----+------+
   |  3 |  OK |  OK  |
   +----+-----+------+
   1 row in set (0.05 sec)
   
   mysql> select * from diff where s2 = ' OK ';
   +----+-----+------+
   | id | s1  | s2   |
   +----+-----+------+
   |  3 |  OK |  OK  |
   +----+-----+------+
   1 row in set (0.05 sec)
   
   mysql> select * from diff where s1 = ' OK';
   +----+-----+------+
   | id | s1  | s2   |
   +----+-----+------+
   |  3 |  OK |  OK  |
   +----+-----+------+
   1 row in set (0.05 sec)
   
   mysql> select * from diff where s2 = ' OK';
   +----+-----+------+
   | id | s1  | s2   |
   +----+-----+------+
   |  3 |  OK |  OK  |
   +----+-----+------+
   1 row in set (0.04 sec)

测试结果可能让人出乎意料，虽然s1和s2中存储的内容不同（差一个空格），但是查找时的行为却完全一样，这说明查找时尾部的空格并不会被考虑。

char和varchar区别

做了半天试验发现char和varchar还是没有多大区别，实际上有些区别通过表面数据是测试不出来的，具体区别整理如下：

行为	char字段	varchar字段
最大长度	255字符	65535个字节，所以括号中最大的字符数还得通过编码来算
是否定长	定长，不足的部分用隐藏空格填充	不定长
空间使用	会有浪费	更加节省
查找效率	高	低
尾部空格	插入时省略	插入时不会省略，查找时省略
like查找	语句中like后的’ ‘不会省	语句中like后的’ ‘不会省，字段结尾的空格也不会省

总结

1. char(n)中的n是字符数，范围是0~255（额外需要1到2个字节来存长度）
2. varchar(n)中的n也是字符数，但是最大值需要通过编码来算，不能超过65535字节（从中还需要拿出1到2个字节来存长度）
3. 一般定长的数据选用char类型，比如身份证号，手机号，电话等，长度变化很大的可以使用varchar类型
4. 注意尾部空格的匹配，特别是插入时和使用like查找时