Mysql中char和varchar的区别

Practical 同时被 2 个专栏收录
43 篇文章 1 订阅
23 篇文章 1 订阅

前言

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

问题起因

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

有点跑题了,实际上是在研究索引长度的过程中,我发现我对charvarchar这两个类型一直存在着误解,因为一直做游戏开发的缘故,游戏数据的存储一般使用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用来统计字段中的字符数,接下来我们用一个例子来看看这两个函数以及charvarchar的区别。

测试环境

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 © 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. 首先创建一个带有charvarchar类型的测试表,查看表结构发现编码为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)
  1. 插入少于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)
  1. 增加插入长度后发现,可以超过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)
  1. 分别增加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>
  1. 至此没有看出区别,在插入内容前后都加上空格测试一下
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查找时
  • 12
    点赞
  • 6
    评论
  • 31
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值