MySQL字符排序:问号前的数字

Question

我最近升级了一个MySQL数据存储，从一些令人讨厌的多年过时的版本升级到8.0.26。

在一个特定的表中，我存储与每个记录相关联的日期，但偶尔也会有未知的未来日期。它们总是以YYYY？？格式存储，因此字段类型是VARCHAR(10)而不是DATE，如果可以始终准确的话，这是可以预期的。字段数据在其他情况下是可靠的YYYY DD。

然而，对此数据排序的查询最近停止了预期的工作，MySQL估计这种未知日期应该在确切日期之前排序。

一个查询归结为如下所示：SELECT * FROM table WHERE date_field <= CURDATE()

(今天是12月3日，所以CURDATE将评估为2021-12-03。在使用文本字符串值2021-12-03而不是CURDATE函数时也会发生同样的情况，因此这肯定是排序问题，而不是数据类型之间的冲突。)

在以前运行的那些旧的MySQL版本中，2021-12-？会比03这样的确切日期更高/更大，因此不会被返回。这也将与ASCII排序保持一致。然而，现在有这样的情况吗？还会返回记录，问号字符显然与以前/小于一个数字排序。

目前，我可以通过在查询中使用REPLACE来强制执行正确和预期的行为，但这是一个过程繁重、丑陋和不方便的问题：SELECT * FROM table WHERE REPLACE(date_field , '??', '99') <= CURDATE()。

有人能解释一下为什么会发生这种情况吗?我会怎么纠正呢？这大概是一个MySQL错误，考虑到标准的ASCII排序和以前的经验(多年的存在)它的工作正常吗？

编辑:感谢最初的回复，指出我要整理。数据库使用的几乎完全是纯朴的英语，只有偶尔的重音(等等)，所以我以前很少接触默认设置。

根据ProGu和lvaroález的答复，我已经开始研究，并且测试没有真正的表/数据库的查询确实会像建议的那样返回。然而，当我试图在真正的表上运行任何东西时，它仍然没有像预期的那样运行。

该表位于InnoDB上，数据库中的所有表和(文本)字段都是utf8mb4/utf8mb4_0900_ai_ci。我尝试过在查询级别以及通过更改实际的表和字段排序规则来强制排序，但是无论我选择哪种方法，都会返回讨厌的2021-12-??。我尝试过各种查询格式，但都没有效果：

SELECT * FROM table WHERE date_field <= CURDATE() ORDER BY date_field COLLATE utf8mb4_0900_ai_ci DESC

SELECT * FROM table WHERE date_field COLLATE utf8mb4_0900_ai_ci <= CURDATE() COLLATE utf8mb4_0900_ai_ci ORDER BY date_field COLLATE utf8mb4_0900_ai_ci DESC

基于lvaro代码的测试，正确返回2021-12-03

with sample_data (sample_value) as (
    select '2021-12-??'
    union all select '2021-12-03'
)
select *
from sample_data
where sample_value <= CURDATE()
order by sample_value COLLATE utf8mb4_0900_ai_ci DESC LIMIT 1;

我的校对经验是否显示；我是否遗漏了一些很明显的东西？

编辑2

所有表和所有文本列(加连接)都已设置为utf8mb4和utf8mb4_0900_ai_ci。

请参见这个DB Fiddle，它还错误地返回2021-12-??作为明显小于比较值(当前日期，2021-12-08)。我无法找到返回实际较小值(示例数据中的2021-10-31)的排序规则。

回到瑞克最初的回答：SELECT "2021-12-??" < "2021-12-03"

这将返回1，即03大于??。为什么？ASCII排序很清楚，数字字符出现在问号字符之前-小于-问号字符。

与我文章的原始版本一样，在我看来，当MySQL将数字用作字符串而不是int时，它的字符排序是错误的。

比较：

SELECT "?" < "0"; =1

SELECT "?" < 0; =0

Answer 1

我想你需要稍微修改一下代码。不清楚以下哪一种情况适用于您的代码。

mysql> SELECT "2021-12-??" < "2021-12-03";
+-----------------------------+
| "2021-12-??" < "2021-12-03" |
+-----------------------------+
|                           1 |
+-----------------------------+

mysql> SELECT "2021-12-??" < CURDATE();
ERROR 1525 (HY000): Incorrect DATE value: '2021-12-??'

mysql> SELECT "2021-12-??" < CAST(CURDATE() AS CHAR);
+----------------------------------------+
| "2021-12-??" < CAST(CURDATE() AS CHAR) |
+----------------------------------------+
|                                      1 |
+----------------------------------------+

mysql> SELECT DATE(NOW()) <= CAST(CURDATE() AS CHAR);
+----------------------------------------+
| DATE(NOW()) <= CAST(CURDATE() AS CHAR) |
+----------------------------------------+
|                                      1 |
+----------------------------------------+