mysql字符集补齐属性导致的bug

一、问题背景：

在更新数据时，将name由'abc '改为'abc'，去除了空格，这个时候，接口报错：“已存在的名称”。

根据代码，发现有个逻辑，会判断名称是否已经在数据库中存在

--javascripttypescriptbashsqljsonhtmlcssccppjavarubypythongorustmarkdown

select count(1) from user_table  where name  = #{name}

于是，我把sql拿到本地执行，发现执行如下sql，查询到只有 name ='abc ' (末尾多了空格)这一行数据。

--javascripttypescriptbashsqljsonhtmlcssccppjavarubypythongorustmarkdown

select * from user_table  where name  = 'abc'

可是，为什么mysql的 = 不是精准匹配？'abc' 明显不等于 'abc ' ！

二、问题分析

于是，上网搜了下，mysql的字符集有一个PAD_ATTRIBUTE属性(补齐属性)，其中有个属性是： 是否忽略尾部空格

该属性有2种取值：

• •

  PAD SPACE: 在排序与比较运算中，忽略字符串尾部空格

• •

  NO PAD: 在排序与比较运算中，不忽略字符串尾部空格

具体可以通过如下sql查询

--javascripttypescriptbashsqljsonhtmlcssccppjavarubypythongorustmarkdown

select * from information_schema.COLLATIONS limit 10;

查询结果如下

COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN	PAD_ATTRIBUTE
armscii8_general_ci	armscii8	32	Yes	Yes	1	PAD SPACE
armscii8_bin	armscii8	64		Yes	1	PAD SPACE
ascii_general_ci	ascii	11	Yes	Yes	1	PAD SPACE
ascii_bin	ascii	65		Yes	1	PAD SPACE
big5_chinese_ci	big5	1	Yes	Yes	1	PAD SPACE
big5_bin	big5	84		Yes	1	PAD SPACE
binary	binary	63	Yes	Yes	1	NO PAD
cp1250_general_ci	cp1250	26	Yes	Yes	1	PAD SPACE
cp1250_czech_cs	cp1250	34		Yes	2	PAD SPACE

所以根据字段所在的字符集的PAD_ATTRIBUTE的值，在对比的时候会出现不同的对比结果。

如下，是一个证明：

--javascripttypescriptbashsqljsonhtmlcssccppjavarubypythongorustmarkdown

select 'a' = 'a '; //返回0,代表两个值不相等
select 'a' = 'a ' COLLATE utf8mb4_general_ci ; //该字符集的PAD_ATTRIBUTE=PAD SPACE,返回1，代表2个值相等。该字符集忽略了末尾的空格再对比的
select 'a' = 'a ' COLLATE utf8mb4_0900_ai_ci ; //该字符集的PAD_ATTRIBUTE=NO PAD,返回0，代表2个值不相等

三、如何解决

既然发现了有这么个问题，如何避免或解决，实现精准匹配？

解决方案：

1. 1.

   使用like

   --javascripttypescriptbashsqljsonhtmlcssccppjavarubypythongorustmarkdown

   select * from table_a where field_name1 like 'abc';  //用like方式

2. 2.

   使用binary关键字

   --javascripttypescriptbashsqljsonhtmlcssccppjavarubypythongorustmarkdown

   select * from table_a where field_name1 = binary 'abc'