我在MySQL中排序一个字符字段时遇到了困难。问题在于带重音符号的字符与不带重音符号的字符混淆在一起。例如:
Abc
Ábd
Acc
我认为这可能与排序规则有关,因此我根据这篇文章的阅读内容,将表的排序规则更改为
utf8-ut8_bin
。事实上,我多次更改了表的排序规则,但都不成功。我还应该补充一点,只要排序不会导致混合列表,我就不介意排序顺序。换句话说,下面这个顺序也可以:
Ábd
Abc
Acc
并且这个也是:
Abc
Acc
Ábd
期待您的回复。
我在MySQL中排序一个字符字段时遇到了困难。问题在于带重音符号的字符与不带重音符号的字符混淆在一起。例如:
Abc
Ábd
Acc
utf8-ut8_bin
。事实上,我多次更改了表的排序规则,但都不成功。Ábd
Abc
Acc
并且这个也是:
Abc
Acc
Ábd
期待您的回复。
utf8_general_cs
。utf8_general_cs
,但utf8_bin
应该是可以的。mysql> SELECT * FROM (
-> SELECT 'A' as l
-> UNION ALL
-> SELECT 'á' as l
-> UNION ALL
-> SELECT 'A' as l) ls
-> ORDER BY l;
+----+
| l |
+----+
| A |
| á |
| A |
+----+
3 rows in set (0.00 sec)
mysql> SELECT * FROM (
-> SELECT 'A' as l
-> UNION ALL
-> SELECT 'á' as l
-> UNION ALL
-> SELECT 'A' as l) ls
-> ORDER BY l COLLATE utf8_bin;
+----+
| l |
+----+
| A |
| A |
| á |
+----+
3 rows in set (0.00 sec)
SET NAMES utf8 COLLATE utf8_unicode_ci;
SELECT GROUP_CONCAT(l SEPARATOR '=') AS gc
FROM (
SELECT 'A' as l UNION ALL
SELECT 'á' as l UNION ALL
SELECT 'A' as l ) ls
GROUP BY l
ORDER BY gc;
+--------+
| gc |
+--------+
| A=á=A |
+--------+
二进制:
SET NAMES utf8 COLLATE utf8_bin;
SELECT GROUP_CONCAT(l SEPARATOR '=') AS gc
FROM (
SELECT 'A' as l UNION ALL
SELECT 'á' as l UNION ALL
SELECT 'A' as l ) ls
GROUP BY l
ORDER BY gc;
+------+
| gc |
+------+
| A=A |
| á |
+------+
(您可以在GROUP_CONCAT
中添加DISTINCT
以避免重复的A
。)
这里是utf8排序规则的完整概述(使用该技术):http://mysql.rjweb.org/utf8_collations.html
建议您在https://bugs.mysql.com/bug.php?id=58797上点击“影响我”。