性能文章>记一次字符串末尾空白丢失的排查 → MySQL 是会玩的!>

记一次字符串末尾空白丢失的排查 → MySQL 是会玩的!原创

7月前
218134

问题背景

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

  他说:明明表名的入参是 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.7 The 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 不参与字符串末尾空格的处理

   MySQL8 server 维度的字符集是 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字符集设置

点赞收藏
青石路
请先登录,查看3条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

聊聊数据库连接池 Druid

聊聊数据库连接池 Druid

4
3