记一次mysql字符串末尾空白丢失的排查

这篇文章主要介绍了记一次mysql字符串末尾空白丢失的排查的相关资料,需要的朋友可以参考下

问题背景

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

　　他说：明明表名的入参是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字符集设置

到此这篇关于记一次mysql字符串末尾空白丢失的排查的文章就介绍到这了,更多相关mysql字符串末尾空白丢失的排查内容请搜索一聚教程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持一聚教程网！