MySQL中相同A < B表达式在不同上下文返回不同结果的原因咨询

问题现象 #

在MySQL 8.0中，字符串<比较出现违反直觉的差异：相同操作数在直接SELECT语句和表查询的WHERE子句中返回完全相反的结果。单独执行字符串比较时'0002/a' < '0002:'返回TRUE，但在WHERE s < '0002:'条件下，表中'0002/a'、'0002/b'却未被筛选出来，该问题在MySQL 5.7中无法复现。

测试SQL代码 #

DROP TABLE IF EXISTS t1;

CREATE TABLE
    t1 (s VARCHAR(10));

INSERT INTO
    t1 (s)
VALUES
    ('0001/a'),
    ('0001/b'),
    ('0002/a'),
    ('0002/b'),
    ('0003/a'),
    ('0003/b');

SELECT ('/' < ':'); -- => `TRUE`

SELECT ('0002/a' < '0002:'); -- => `TRUE`

SELECT ('0002/b' < '0002:'); -- => `TRUE`

SELECT * FROM t1 WHERE (s < '0002:'); -- => 结果与预期不符

预期结果 #

+--------+
| s      |
+--------+
| 0001/a |
| 0001/b |
| 0002/a |
| 0002/b |
+--------+

实际结果 #

+--------+
| s      |
+--------+
| 0001/a |
| 0001/b |
+--------+

环境信息 #

• 使用无特殊配置的Docker版MySQL：

$ docker run -d -p 3306:3306 --name mysql \
  -e 'MYSQL_DATABASE=t' \
  -e 'MYSQL_ROOT_PASSWORD=password' \
  --restart always mysql

• 排序规则配置：

> show variables like "%collat%"
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb3_general_ci |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+

原因分析 #

核心问题是排序规则不统一：

1. 直接SELECT中的字符串比较，使用的是当前连接的排序规则utf8mb3_general_ci，在该规则下'/'的排序权重小于':'，因此'0002/a' < '0002:'返回TRUE。
2. 表查询的WHERE子句中，比较时会使用列s的排序规则（建表时未指定，继承自数据库的utf8mb4_0900_ai_ci）。这是MySQL 8.0默认的Unicode 9.0排序规则，其中':'的排序权重小于'/'，因此'0002/a' > '0002:'，导致这些行不会被筛选出来。

可以通过显式指定排序规则验证：

-- 使用列的排序规则，返回FALSE
SELECT ('0002/a' < '0002:') COLLATE utf8mb4_0900_ai_ci;

解决方法 #

• 统一排序规则：将连接的排序规则修改为与数据库/表一致，例如执行SET collation_connection = 'utf8mb4_0900_ai_ci';，或在连接数据库时指定该排序规则。
• 显式指定排序规则：在比较时明确指定使用的排序规则，例如：

  SELECT * FROM t1 WHERE s < '0002:' COLLATE utf8mb3_general_ci;
  

• 建表时指定排序规则：创建表时为列指定所需的排序规则，例如：

  CREATE TABLE t1 (s VARCHAR(10) COLLATE utf8mb3_general_ci);
  

内容的提问来源于stack exchange，提问作者ynn