开心一刻

  今天答应准时回家和老婆一起吃晚饭,但临时有事加了会班,回家晚了点

  回到家,本以为老婆会很生气,但老婆却立即从厨房端出了热着的饭菜

  老婆:还没吃饭吧,去洗下,来吃饭吧

  我洗好,坐下吃饭,内心感动十分;老婆坐旁边深情的看着我

  老婆:你知道谁最爱你吗

  我毫不犹豫道:你

  老婆:谁最关心你?

  我:你

  老婆:我是谁呀?

  我:我老婆

  老婆:那你以后是不是得对我好点?

  这时电话响了,一看好哥们打过来的,我接了并开了免提

  哥们:楼下洗浴八折,干啥呢?

  我:那个……,在陪我前妻吃口饭

问题背景

  一天,小伙伴找到我,他说他碰到一个很奇怪的问题

  他说:明明表名的入参是test ,为什么展示到界面的记录包括test这条记录?

  他补充道:会不会是MyBatis-Plus做了什么骚操作,把test 末尾的空格给拿掉了

  我:你直接把SQL语句到MySQL执行下试试

  结果如下:

  这看起来不够直观,我移动下光标

  然后我和小伙伴面面相觑

环境准备

  MySQL5MySQL8各准备一个

  我们来看下默认情况下,末尾空白的判断情况

  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 SETCOLLATE指定数据库的字符集、字符序

  可以通过

  查看数据库的字符集和字符序

  table的字符集与字符序

  创建、修改表的时候,可以通过CHARACTER SETCOLLATE指定表的字符集、字符序

  可以通过

  查看表的字符序

  column的字符集与字符序

  类型为CHARVARCHARTEXT的列,可以指定字符集、字符序

  可以通过

  查看字段的字符集和字符序

  多个维度指定字符集、字符序的话,粒度越细的优先级越高(column>table>database>server

  如果细粒度未指定字符集、字符序,那么会继承上一级的字符集,字符序则是上一级字符集的默认字符序

  通常情况下我们一般不会指定tablecolumn粒度的字符集、字符序

  也就是说,通常情况下column的字符集会与database的字符集一致,而column的字符序则是database字符集的默认字符序

空白丢失

  上面讲了那么多,跟空白丢失有什么关系?

  大家先莫急,继续往下看

  MySQL5.7The CHAR and VARCHAR Types中有这么一段

  翻译过来就是:

    1、类型是CHARVARCHARTEXT列的值,会根据列的字符序来比较和排序

    2、所有MySQL排序规则的类型都是PAD SPACE。这就意味着,CHARVARCHARTEXT类型的值进行比较时,不用考虑任何末尾空格,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、类型是CHARVARCHARTEXT列的值,会根据列的字符序来比较和排序

    2、MySQL字符序的pad参数的可选值,除了PAD SPACE,还增加了NO PAD

    3、对于非二进制字符串(CHARVARCHARTEXT),字符序pad参数决定如何去处理字符串末尾的空格

      NO PAD不会忽略末尾空格,会将其当做其他字符一样对待

      PAD SPACE会忽略末尾空格,LIKE除外

      SQL mode不参与字符串末尾空格的处理

  MySQL8server 维度的字符集是utf8mb4,对应的默认字符序是:utf8mb4_0900_ai_ci

  Pad_attribute的值是NO PAD,也就是不会忽略字符串末尾的空格

  所以在MySQL8中,SELECT test = test ;默认情况下得到的结果是 0

总结

  1、非二进制字符串(CHARVARCHARTEXT)比较时,末尾空格的处理跟列的字符序有直接关系

  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字符集设置