开心一刻
今天答应准时回家和老婆一起吃晚饭,但临时有事加了会班,回家晚了点
回到家,本以为老婆会很生气,但老婆却立即从厨房端出了热着的饭菜
老婆:还没吃饭吧,去洗下,来吃饭吧
我洗好,坐下吃饭,内心感动十分;老婆坐旁边深情的看着我
老婆:你知道谁最爱你吗
我毫不犹豫道:你
老婆:谁最关心你?
我:你
老婆:我是谁呀?
我:我老婆
老婆:那你以后是不是得对我好点?
这时电话响了,一看好哥们打过来的,我接了并开了免提
哥们:楼下洗浴八折,干啥呢?
我:那个……,在陪我前妻吃口饭
问题背景
一天,小伙伴找到我,他说他碰到一个很奇怪的问题
他说:明明表名的入参是test ,为什么展示到界面的记录包括test这条记录?
他补充道:会不会是MyBatis-Plus做了什么骚操作,把test 末尾的空格给拿掉了
我:你直接把SQL语句到MySQL执行下试试
结果如下:
这看起来不够直观,我移动下光标
然后我和小伙伴面面相觑
环境准备
MySQL5、MySQL8各准备一个
我们来看下默认情况下,末尾空白的判断情况
MySQL 5.7.36如下
1 表示TRUE,也就是相等
MySQL 8.0.27如下
0 表示FALSE,表示不相等
这是什么原因,我们继续往下看
字符集与字符序
比较肯定就需要比较规则,SQL的比较规则就离不开字符序,字符序又与字符集相关,所以我们一个一个来捋
字符集
关于字符集,不是只言片语可以说清楚的,但是大家也不用担心,网上相关资料已经非常多,大家擦亮慧眼去查阅即可
简单点来说:字符集定义了字符和字符的编码
有人又问了:字符、字符的编码又是什么?
为了方便大家理解,举个简单栗子
有四个字符:A、B、C、D,这四个字符的编码分别是 A = 0, B = 1, C = 2, D = 3
这里的字符(A、B、C、D) + 编码(0、1、2、3)就构成了字符集(character set)
MySQL支持的字符集有很多,可以通过SHOW CHARACTER SET;查看
Charset:字符集名
Description:描述
Default collation:默认字符序
Maxlen:每个字符最多字节数
字符序
定义了字符的比较规则;字符间的比较按何种规则进行
一个字符集对应多个字符序,通过SHOW COLLATION;可以查看全部的字符序;也可以带条件查具体某个字符集的字符序
Default等于Yes表示是默认字符序
每个字符集都有默认的字符序
server的字符集与字符序
当我们创建数据库时,没有指定字符集、字符序,那么server字符集、server字符序就会作为该数据库的字符集、字符序
database的字符集与字符序
指定数据库级别的字符集、字符序
同一个MySQL服务下的数据库,可以分别指定不同的字符集、字符序
创建、修改数据库的时候,可以通过CHARACTER SET、COLLATE指定数据库的字符集、字符序
可以通过
查看数据库的字符集和字符序
table的字符集与字符序
创建、修改表的时候,可以通过CHARACTER SET、COLLATE指定表的字符集、字符序
可以通过
查看表的字符序
column的字符集与字符序
类型为CHAR、VARCHAR、TEXT的列,可以指定字符集、字符序
可以通过
查看字段的字符集和字符序
多个维度指定字符集、字符序的话,粒度越细的优先级越高(column>table>database>server)
如果细粒度未指定字符集、字符序,那么会继承上一级的字符集,字符序则是上一级字符集的默认字符序
通常情况下我们一般不会指定table、column粒度的字符集、字符序
也就是说,通常情况下column的字符集会与database的字符集一致,而column的字符序则是database字符集的默认字符序
空白丢失
上面讲了那么多,跟空白丢失有什么关系?
大家先莫急,继续往下看
MySQL5.7The CHAR and VARCHAR Types中有这么一段
翻译过来就是:
1、类型是CHAR、VARCHAR、TEXT列的值,会根据列的字符序来比较和排序
2、所有MySQL排序规则的类型都是PAD SPACE。这就意味着,CHAR、VARCHAR、TEXT类型的值进行比较时,不用考虑任何末尾空格,LIKE除外
3、不受SQL mode影响,也就是说不管是严格模式,还是非严格模式,都不影响 2 所说的规则
划重点,记笔记:在MySQL5.7及以下(<=5.7)版本中,排序规则都是PAD SPACE,末尾的空格会忽略不考虑
那如何让末尾空格参与比较了,有三种处理方式
1、BINARY,类似SELECT ‘test‘ = BINARY ‘test ‘;
2、LIKE,类似SELECT ‘test‘ LIKE ‘test ‘;
3、LENGTH函数,类似
MySQL8做了调整,The CHAR and VARCHAR Types 有如下说明
翻译过来就是:
1、类型是CHAR、VARCHAR、TEXT列的值,会根据列的字符序来比较和排序
2、MySQL字符序的pad参数的可选值,除了PAD SPACE,还增加了NO PAD
3、对于非二进制字符串(CHAR、VARCHAR、TEXT),字符序pad参数决定如何去处理字符串末尾的空格
NO PAD不会忽略末尾空格,会将其当做其他字符一样对待
PAD SPACE会忽略末尾空格,LIKE除外
SQL mode不参与字符串末尾空格的处理
MySQL8server 维度的字符集是utf8mb4,对应的默认字符序是:utf8mb4_0900_ai_ci
Pad_attribute的值是NO PAD,也就是不会忽略字符串末尾的空格
所以在MySQL8中,SELECT ‘test‘ = ‘test ‘;默认情况下得到的结果是 0
总结
1、非二进制字符串(CHAR、VARCHAR、TEXT)比较时,末尾空格的处理跟列的字符序有直接关系
2、MySQL5.7及之前的版本,排序规则的类型都是PAD SPACE,会忽略字符串末尾的空格,LIKE除外
3、MySQL8开始,字符序增加了一个参数Pad_attribute,该参数的值不同,对字符串末尾空格的处理方式不同
NO PAD:字符串末尾的空格会和其他字符一样,不会被忽略
PAD SPACE:字符串末尾的空格会被忽略,LIKE除外
4、如上针对的都是非二进制字符串的排序和比较,而不是储存
参考
The CHAR and VARCHAR Types
The CHAR and VARCHAR Types
再见乱码:5分钟读懂MySQL字符集设置