读MySQL5.7 官方文档11.3.2 The CHAR an

2020-03-11 本文已影响0人小知_知数堂

作者：魏新平，知数堂第5期MySQL实战班学员，第10期MySQL优化班学员，现任职助教。

varchar(n) 和char(n)的最大长度

相信作为dba，经常会被问到这个问题吧。那什么是最大长度。多少个字节还是多少个字符。其实这样讲不是很明白，简单的理解就是能放多少个字。一个字母，一个空格，一个中文字，一个标点符号的长度都是1。那么varchar(2),char(2)最大可以放两个字。那么这个n最大到底可以设置为多少呢。

这里需要先讲一个大前提，就是表的一行最长只能存放65535个字节，这个可不是能放多少个字的意思，由于设置的字符集的不同，可以存放的字的最大数量也是不同的。比如gbk存一个中文是2个字符，而utf8当中一个中文是3个字符.(后面的举例会涉及到这个知识点)

char(n)当中的n设置范围是0到255，也就是说最大可以存放255个字。

varchar(n)当中n的设置范围就不一样了，他没有一个固定的最大值，会随着表当中其他的列所占用的字节数和所设置的字符集而影响到n的最大值。而且varchar自己还要占用一个到两个字符来确定后面数据字节的长度。

接下来我们举例

admin@localhost [weixinping_test]  14:15:01>create table test_char(a char(256));
ERROR 1074  (42000):  Column length too big for column 'a'  (max =  255);  use BLOB or TEXT instead
    
admin@localhost [weixinping_test]  14:15:13>create table test_char(a char(255));
Query OK,  0 rows affected (0.03 sec)

创建表的时候如果使用char,n的值设置超过255就会报错。再来验证一下字节数是不受这个255限制的。我们插入110个汉字，由于我们的字符集是utf8mb4，也就是说一个汉字是3个字节，110汉字就是330个字节，肯定会超过这个255个限制。结果如下

admin@localhost [weixinping_test]  14:15:45>select length(a)  from test_char;
+-----------+
| length(a)  |
+-----------+
|  330  |
+-----------+
1 row in  set  (0.01 sec)

针对varchar的最大值。

admin@localhost [weixinping_test]  14:39:34>create table test_varchar(a varchar(65533)  not  null  )  default CHARSET latin1;
Query OK,  0 rows affected (0.02 sec)
    
admin@localhost [weixinping_test]  14:42:17>create table test_varchar1(a varchar(65533)  not  null  )  default CHARSET utf8mb4;
ERROR 1074  (42000):  Column length too big for column 'a'  (max =  16383);  use BLOB or TEXT instead
    
admin@localhost [weixinping_test]  14:52:43>create table test_varchar2(a varchar(65530)  not  null,b varchar(65530)  not  null  )  default CHARSET latin1;
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呢，因为varchar需要再前面使用两个字节（数据字节数超过255时）或者一个字节（数据小于等于255时）来存储具体的数据的长度，所以需要减去2，为了去掉null标志位的影响，需要设置为not null。第一张表创建成功，第二张表创建失败，唯一的不同点是最后的字符集，latin1一个字使用一个字节，而utf8mb4是变长的，最长的占用字符为4。最大长度就变成了65533/4 约等于16383 。

最后一个创建表的语句失败了，这数字明显是不到65533啊，因为前面说过，行的最大长度有个大前提，一行最大的长度只能是65535字节，这个是所有列共享的。

长度的事情说完了，再来说说空格和大小写的问题。

空格问题

在char类型当中，最后面的空格会被忽略，不管是存储到数据库当中，还是拿出来的时候。如下

admin@localhost [weixinping_test]  15:05:24>insert into test_char() values('ac '),('ac     '),('a  c '),('    ac ');
Query OK,  4 rows affected (0.00 sec)
Records:  4  Duplicates:  0  Warnings:  0
    
admin@localhost [weixinping_test]  15:05:58>select LENGTH(a),a from test_char;
+-----------+--------+
| LENGTH(a)  | a |
+-----------+--------+
|  2  | ac |
|  2  | ac |
|  4  | a  c |
|  6  | ac |
+-----------+--------+
4 rows in  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:06:05>

但是在varchar会显的真实一点，是什么就是什么。也就是说会存储空格，拿出来的时候也会显示。

admin@localhost [weixinping_test]  15:07:42>insert into test_varchar() values('ac '),('ac     '),('a  c '),('    ac ');
Query OK,  4 rows affected (0.00 sec)
Records:  4  Duplicates:  0  Warnings:  0
    
admin@localhost [weixinping_test]  15:07:53>select LENGTH(a),a from test_varchar;
+-----------+---------+
| LENGTH(a)  | a |
+-----------+---------+
|  3  | ac |
|  7  | ac |
|  5  | a  c |
|  7  | ac |
+-----------+---------+
4 rows in  set  (0.00 sec)

我们插入数据以后，很多情况下会去比对这些字段，比如where a = '你好'啥的，但是涉及到空格的话，又会有哪些结果出来呢。

admin@localhost [weixinping_test]  15:12:03>select  *  from test_char where a =  'ac    ';
+------+
| a |
+------+
| ac |
| ac |
+------+
2 rows in  set  (0.01 sec)
    
admin@localhost [weixinping_test]  15:12:43>select  *  from test_char where a =  'ac';
+------+
| a |
+------+
| ac |
| ac |
+------+
2 rows in  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:12:48>select  *  from test_char where a like 'ac %';
Empty  set  (0.00 sec)

对于char来说,再使用=的时候，会忽略里面字符的空格，但是使用like就不会了。由于中间和前面的空格，会存储进去，所以比较的时候也会影响到这个，所以第一条和第二条都没有显示出‘ ac’的结果。只有两个ac的结果。

那varchar呢，按照道理来说，由于存储的时候会存储空格，取出来的时候也会有空格，那就是说比对也应该考虑到最后的空格的问题。看下面的结果

admin@localhost [weixinping_test]  15:19:12>select LENGTH(a),a from test_varchar where a =  'ac';
+-----------+---------+
| LENGTH(a)  | a |
+-----------+---------+
|  3  | ac |
|  7  | ac |
+-----------+---------+
2 rows in  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:19:34>select LENGTH(a),a from test_varchar where a =  'ac                ';
+-----------+---------+
| LENGTH(a)  | a |
+-----------+---------+
|  3  | ac |
|  7  | ac |
+-----------+---------+
2 rows in  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:19:49>select LENGTH(a),a from test_varchar where a =  ' ac                ';
Empty  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:20:05>select LENGTH(a),a from test_varchar where a =  ' a c                ';
Empty  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:20:15>select LENGTH(a),a from test_varchar where a like 'ac                ';
Empty  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:23:03>select LENGTH(a),a from test_varchar where a like 'ac    %';
Empty  set  (0.00 sec)

和char的表现一样，如果是后面的空格，不管我放几个空格，都会出现同样的结果，两个ac。但是如果我把空格放到前面和中间，空格在比较时就不会被忽略了。但是like的时候都是会考虑到最后的空格的。

再来说说大小写的问题吧

默认情况下，如果不做任何配置修改的情况下，字符串当中的大小写是不区分的。

admin@localhost [weixinping_test]  15:35:13>insert into test_char() values('a'),('A');
Query OK,  2 rows affected (0.00 sec)
Records:  2  Duplicates:  0  Warnings:  0
    
admin@localhost [weixinping_test]  15:35:20>select  *  from test_char where a =  'a'
->  ;
+------+
| a |
+------+
| a |
| A |
+------+
2 rows in  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:35:34>select  *  from test_char where a =  'A';
+------+
| a |
+------+
| a |
| A |
+------+
2 rows in  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:35:53>select  *  from test_char where a like 'a';
+------+
| a |
+------+
| a |
| A |
+------+
2 rows in  set  (0.01 sec)
    
admin@localhost [weixinping_test]  15:41:18>insert into test_varchar(a) values('a'),('A');
Query OK,  2 rows affected (0.00 sec)
Records:  2  Duplicates:  0  Warnings:  0
    
admin@localhost [weixinping_test]  15:42:01>select  *  from test_varchar where a =  'a';
+---+
| a |
+---+
| a |
| A |
+---+
2 rows in  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:42:16>select  *  from test_varchar where a =  'A';
+---+
| a |
+---+
| a |
| A |
+---+
2 rows in  set  (0.00 sec)
    
admin@localhost [weixinping_test]  15:42:20>select  *  from test_varchar where a like 'A';
+---+
| a |
+---+
| a |
| A |
+---+
2 rows in  set  (0.00 sec)

可以看到存进去是有大小区分的，但是去取的时候，是不区分大小写的，都可以获得结果。就算是like也是一样的结果。varchar的结果和char的结果是一样的。

那么如果我要区分大小写了，怎么办呢。这里有两个办法，一个是修改表结构，一个是修改sql语句。修改表结构的话，提示一下是取修改字符集后面的那个COLLATE，这个下一篇文章再说吧。说一个更这个简单的方法吧，直接使用binary关键字。如下

admin@localhost [weixinping_test]  15:50:49>select  *  from test_varchar where binary a =  'A';
+---+
| a |
+---+
| A |
+---+
1 row in  set  (0.00 sec)

最后总结一下

char的最大长度是255（这个长度是字符或者说是多少个字，不是字节数）。
varchar的最大长度是可变的，由于行最大长度65535字节的要求，字符集的变化会不一样。
字符串最后的空格在char当中不会被保留，varchar当中是会被保留的，但是使用=比对时，都会自动忽略条件后面的空格。不过like是会考虑后面的空格的。
默认配置下，字符串配置是不区分大小写的，最简单的修改方法是在使用sql的时候使用binary关键字。