坑爹的字符集问题：踩到了 MySQL 的 bug

有意思，学习了。

你没想过 8.0 默认的是 utf8mb4_0900_ai_ci 或者以前用 utf8mb4_general_ci 吗

用这个就好了: utf8mb4_0900_bin

不可能改 utf8mb4_bin 的行为，你自己也查了，这个的 Pad_attribute 是 PAD SPACE ，改了就不一致了

@wqtacc #2 你说的对，但是不能否认 utf8mb4_bin 它确实有 bug 。其实不止这一个有问题，可以试试 gbk_bin 、gb18030_bin 等，也是一样的。

@Nasei #3 字符集排序规则行为是不能改变。但是如果改变 like 转化为范围查询的内部逻辑呢？那就是可行的了。其实是能修的。

借楼问一下，现在 mysql8 大家在建库建表时一般使用哪种排序规则了。mysql8 默认变成 utf8mb4_0900_ai_ci

create table t1 ( c1 varchar(16), key idx (c1) ) collate=utf8mb4_general_ci;

insert into t1 values ('000\n'), ('123\n'), ('abc\n');

select * from t1 where c1 like 'abc%';


set collation_connection=utf8mb4_general_ci;

select 'abc\n' < 'abc';


mysql 8.2.0 为什么在 utf8mb4_general_ci 规则下
select 'abc\n' < 'abc';
返回 1
但是
select * from t1 where c1 like 'abc%';
仍然能够查到值，没看懂

@Nasei utf8mb4_0900_bin 内部 pad 不 pad ，pad 0x00 吗？

MySQL 无处不是坑

@wuyiccc #6
因为其实问题不在这（虽然有一定逻辑关系在里面），问题在于查询优化。

可以看一下 my_like_range_mb() 的实现，它的注释里面有：

> "a" is the smallest possible string for NO PAD.
> "a\0\0..." is the smallest possible string for PAD SPACE.
> "a\xff\xff..." is the biggest possible string.

其实 MySQL 是意识到这个问题的。但是后面的 if 条件是 (cs->state & MY_CS_BINSORT) || cs->pad_attribute == NO_PAD
单独把 MY_CS_BINSORT 加入判断，我觉得这个是没有理由的，且造成了 bug 。我的 patch 就是把它去掉了，测试用例可以通过。

@lepig #5
如果是全新的业务，那么用默认的排序规则 utf8mb4_0900_ai_ci ，或者它的哥们（大小写敏感/不敏感）都是 OK 的。
这里考虑到一些已有的老库，比如它们的排序规则已经定在 utf8mb4_bin 了，那么除非重新进行完整的测试，那最好还是别动它，一般还按原来的用。

@mikewang OK 。

学到了👍

mysql 的字符集坑多的很

-> Filter: (my_table_temp.`name` like 'abc%') (cost=0.46 rows=1)
-> Covering index range scan on my_table_temp using idx over ('abc' <= name <= 'abc￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿') (cost=0.46 rows=1)

@mikewang #4 嗯你说的是对的

@kinkin666 属性是 no pad

所以我一直不用 utf8mb4_bin ，总感觉这玩意很怪。

大意了，我们 TiDB 竟然忘了兼容这个 MySQL bug……

nightly 版本测试

```
mysql> create table t1 ( c1 varchar(16), key idx (c1) ) collate=utf8mb4_bin;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values ('000\n'), ('123\n'), ('abc\n');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1 where c1 like 'abc%';
+------+
| c1 |
+------+
| abc
|
+------+
1 row in set (0.00 sec)

mysql> explain select * from t1 where c1 like 'abc%';
+--------------------------+---------+-----------+-------------------------+-----------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------+---------+-----------+-------------------------+-----------------------------------------------------+
| IndexReader_7 | 250.00 | root | | index:Selection_6 |
| └─Selection_6 | 250.00 | cop[tikv] | | like(test.t1.c1, "abc%", 92) |
| └─IndexRangeScan_5 | 250.00 | cop[tikv] | table:t1, index:idx(c1) | range:["abc","abd"), keep order:false, stats:pseudo |
+--------------------------+---------+-----------+-------------------------+-----------------------------------------------------+
3 rows in set (0.00 sec)

mysql> alter table t1 drop index idx;
Query OK, 0 rows affected (0.11 sec)

mysql> select * from t1 where c1 like 'abc%';
+------+
| c1 |
+------+
| abc
|
+------+
1 row in set (0.01 sec)

mysql> explain select * from t1 where c1 like 'abc%';
+-------------------------+---------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+---------+-----------+---------------+--------------------------------+
| TableReader_7 | 0.07 | root | | data:Selection_6 |
| └─Selection_6 | 0.07 | cop[tikv] | | like(test.t1.c1, "abc%", 92) |
| └─TableFullScan_5 | 3.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-------------------------+---------+-----------+---------------+--------------------------------+
3 rows in set (0.01 sec)
```

@lance6716 #18 优秀 d(^_^o)

前些年发现的：
/t/725189 《多灾多难，今天又来了零宽字符，导致字符串手机号在数据库查询不出结果》

/t/724866 《发现多种数据库 group by 对字符串首尾空格的坑死人不偿命规范》

有些空白/零宽是 trim 不掉的，不同开发语言 tirm 的默认字符范围还不一样，反正到了数据库就会有奇奇怪怪但又很难发现的 bug

@xiangyuecn #20
你确实发现了一个令很多人困惑的地方：SQL 中的“相等”，其实是概念上的等价，不是完全的相同。

排序规则（ collation ），规定了 order by 的顺序，也规定了哪些字符串是“相等”的，等号运算会返回真，group by 会被分到一组。
如果对排序规则了解不深，我推荐阅读一些相关的资料，这样奇奇怪怪的问题就能迎刃而解了。

以 MySQL 为例吧，设置 set collation_connection=utf8mb4_general_ci;
select 'abc' = 'ABC'; -- 你会发现它们是相等的，结果为 1 。
select hex('abc') = hex('ABC'); -- 你会发现结果为 0 ，不等。

select 'abc' = 'abc '; -- 相等
select length('abc') = length('abc '); -- 不等

其实也没什么问题。因为“排序规则”规定了它们等价。但是它们又不是同一个东西，所以套一层函数就不等了。
当然你也可以选一个 NO PAD 的规则，让它们自身不等。

set collation_connection=utf8mb4_0900_ai_ci;
select 'abc' = 'abc '; -- 不等
select length('abc') = length('abc '); -- 不等

这些都是可以配置的，并且不同数据库上的默认配置可能有所不同。

希望对你有所帮助。

学到了，感谢分享！