昨天的 /t/1133223 吸引了大家不少的讨论，今天我来说一个工作上遇到的问题。
准确地说，这个问题是 MySQL 字符集中的校对规则出了 BUG ，字符集本身是无辜的。
这个 bug 现在都还在，欢迎大家验证哈。

故事是这样的。
同事在连 MySQL 库做测试时发现了一个诡异的现象：查不到匹配的数据。
相关语句简化如下（主键等字段已省略）：
create table t1 ( c1 varchar(16), key idx (c1) ) collate=utf8mb4_bin;

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

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

这怎么看，都应该匹配出 'abc\n'，对吧？
事实情况是：
mysql> select * from t1 where c1 like 'abc%';
Empty set (0.00 sec)

天塌了，查出来竟然是空的。
然后我拿同样的语句在 OceanBase 上跑了一下，竟然也是空。（两眼一黑）

可能会有人说，那肯定是你写的语句有问题，或者 utf8mb4_bin 就这样，吧啦吧啦。
那如果这样呢：
mysql> alter table t1 drop index idx;
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

1 row in set (0.001 sec)

哎，索引删了就好了。
总不能说，加个索引，能把结果集搞没吧。那肯定 bug 了。

那到底是咋回事呢：带上索引，我们 explain 看一下。
mysql> explain format=tree select * from t1 where c1 like 'abc%' \G
1. row **
EXPLAIN: -> Filter: (t1.c1 like 'abc%') (cost=0.46 rows=1)
-> Covering index range scan on t1 using idx over ('abc' <= c1 <= 'abc?????????????') (cost=0.46 rows=1)

1 row in set (0.001 sec)

原来这个前置匹配，因为有索引，优化为了范围查询。后面的一串 ? 其实是 0xff，没什么问题。
那看下 'abc\n' 和 'abc' 呢？
mysql> set collation_connection=utf8mb4_bin;
Query OK, 0 rows affected (0.000 sec)

mysql> select 'abc\n' < 'abc';
'abc\n' < 'abc'
1

1 row in set (0.000 sec)

再次两眼一黑。我倒，怎么会这样。这是什么排序规则。看下 utf8mb4_bin 吧。

mysql> show collation like 'utf8mb4_bin';
CollationCharsetIdDefaultCompiledSortlenPad_attribute
utf8mb4_binutf8mb446 Yes1PAD SPACE

1 row in set (0.001 sec)

Pad_attribute 是 PAD SPACE，表示对齐长度时，后面补空格。这下就说通了。空格是 0x20，换行符是 0x0a。\n 比 小。
所以！！虽然反直觉，在 utf8mb4_bin 下，'abc\n' 就是 'abc' 小！
结论：like 'abc%' 的范围查询优化有问题。

关于这个 bug ，我已经向 MySQL 提交了 patch ，但是似乎没有得到关注。我看了下更新日志，我提的另一个 patch 已经被合入，但是这个问题依然还在。看来涉及到字符集，这个坑麻烦到他们都不想处理了。

[ 同一时间提交的代码已经合入 ]

[ 这个问题还是打开的 ]

如果哪天他们合入或者解决了，我再 append 新的进展。

有意思，学习了。

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

用这个就好了: utf8mb4_0900_bin

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

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

＃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%';
仍然能够查到值，没看懂

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

MySQL 无处不是坑

＃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 就是把它去掉了，测试用例可以通过。

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

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)

＃4 嗯你说的是对的

属性是 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)

＃18 优秀 d(^_^o)

前些年发现的：
/t/725189 《多灾多难，今天又来了零宽字符，导致字符串手机号在数据库查询不出结果》
/t/724866 《发现多种数据库 group by 对字符串首尾空格的坑死人不偿命规范》

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