时间戳

首页 > 解决方案 > 在 MySQL 5.6 中将整数与显式排序规则进行比较时如何避免无效错误

mysql - 在 MySQL 5.6 中将整数与显式排序规则进行比较时如何避免无效错误

问题描述

好吧，这不是最清晰的标题；随时改进。

我有一张代表数千种语言形式的表格。其中许多都大量使用变音符号，因此所有aha、áha̱和ā̧́ḫà̀都可能出现。该表（和数据库）使用 UTF-8 作为字符集和utf8mb4_unicode_520_ci默认排序规则，因为搜索应该是不区分大小写和变音符号的（因此搜索aha应该会显示所有三个）。不过这些表格都是人工输入的，难免有重复。

我目前正在尝试获取完全相同的表单列表以消除重复项（手动 - 每个标记在被删除之前都必须检查），但在这种情况下，我需要以变音符号感知方式进行搜索 -也就是说，鉴于上面列出的三个标记，我希望搜索不会产生任何结果，因为由于变音符号，它们是三种不同的形式。

我认为这应该是一项相当容易的任务；做就是了：

SELECT token FROM table GROUP BY token HAVING COUNT(token) > 1 COLLATE utf8mb4_bin

但很可惜，这行不通。相反，它给了我一条错误消息“COLLATION utf8mb4_bin is not valid for CHARACTER SET latin1”。我应该注意，我在任何地方都没有 Latin-1 ——没有字符集，没有排序规则，没有服务器字符集，什么都没有。也没有存储过程或任何其他可能会出现 Latin-1 的东西。

不，这是因为这个错误，显然从 5.7 开始修复；见底部的描述：

对于 ORDER BY numeric_expr COLLATE collat​​ion_name 等构造，表达式的字符集被视为 latin1，如果在 COLLATE 之后指定的排序规则与 latin1 不兼容，则会导致错误。现在，当存在 COLLATE 的情况下将数值表达式隐式转换为字符表达式时，所使用的字符集就是与命名排序规则相关联的字符集。

不幸的是，我在 5.6 上，我没有升级的选项（很烦人）。将数据转换为 Latin-1 也不是一种选择，也不是更改表上的排序规则。

有没有一种方法可以运行我的查询或产生我所追求的结果集的等效查询，而不会出现排序规则错误？

标签： mysqlcharacter-encodingutf8mb4

解决方案

SET NAMES utf8mb4;
CREATE TABLE x(s VARCHAR(11) COLLATE utf8mb4_unicode_520_ci NOT NULL);
INSERT INTO x (s)
    VALUES ('aha'), ('áha̱'), ('ā̧́ḫà̀'),
    ('i'), ('i̯');
SELECT s FROM x GROUP BY s HAVING COUNT(*) > 1;

回来了

啊哈我

没有任何关于数字的东西的抱怨。

我在 5.6.46、5.7.26、8.0.16 和几个 MariaDB 版本上运行它。

我在做什么与你的情况不同？

添加COLLATE显式子句时，将其放在需要它的查询组件上。（COLLATE不适用于整个查询；不同的部分可以进行不同的整理。）

推荐阅读