GBK 表中如何用 GROUP BY 找出 UTF-8 下相同 username 的记录？

mysql 中 collate 用法的困惑

对于 mysql 数据库中字符集和排序规则的处理，可能会遇到一些困惑。下面以 collate 用法为例，针对一个具体问题进行解答。

问题：

在一个 gbk 字符集的表中，存在两个 username 值：forever 和 fòrēvèr，它们在 gbk 字符集下被视为不同的字符。但如果使用 utf-8 字符集，则它们被识别为同一字符。现在需要找出表中所有 username "相同" 的记录，但 group by username collate utf8mb4_general_ci 无法正常执行。查询是否存在不用 collate 替代方法来解决此问题？

解答：

当使用 group by 语句时，需要确保对分组合并列进行排序和比较，以确定哪些记录属于同一组。在这种情况下，目标是在 utf-8 字符集下比较 username 值。然而，表本身的字符集是 gbk，这会导致比较不一致。

为了解决此问题，可以使用以下查询：

select convert(username, char character set utf8mb4) collate utf8mb4_general_ci
from test.user
group by convert(username, char character set utf8mb4) collate utf8mb4_general_ci;

该查询执行以下操作：

• 使用 convert() 函数将 username 值转换为 utf-8 字符集。
• 在转换为 utf-8 字符集后，使用 collate 子句指定 utf8mb4_general_ci 排序规则。
• 使用 group by 子句对转换后的值进行分组，从而确保在 utf-8 字符集下比较 username 值。

通过这种方法，可以正确找出表中 username "相同" 的记录，而无需使用 collate 子句对 group by 语句进行修饰。