MySQL排序顺序-排序规则? [英] MySQL Sort Order - Collation?

问题描述

我在MySQL中对char字段进行排序时遇到了困难.问题在于，带重音符号的字符与未带重音符号的字符混合在一起.例如:

I'm having a difficult time sorting a char field in MySQL. The problem is that accented characters get mixed up with un-accented characters. For example:

Abc
Ábd
Acc

我认为这可能与归类有关.所以在阅读

I thought it may have something to do with collation. So I changed the collation of my table to utf8-ut8_bin, after reading this post. Actually, I altered the table several times to various collations. No cigar.

我还应该补充一点，我不介意排序的顺序，只要排序不会导致混合列表.换句话说，这很好:

I should also add that, I don't mind the order of the sort as long as the sort doesn't result in a mixed list. In other words, this is fine:

Ábd
Abc
Acc

也是这样:

Abc
Acc
Ábd

期待您的回复.

推荐答案

您只需要使用区分大小写的排序规则，例如:utf8_general_cs.

You just need to use a case-sensitive collation, for example: utf8_general_cs.

UPD

很抱歉，似乎没有utf8_general_cs，但是utf8_bin应该可以工作.

I am sorry, it seems there is no utf8_general_cs, utf8_bin should work though.

并且您应该更改特定字段的排序规则，而不是表的排序规则(或确保该字段确实使用表的默认值).

And you should change the collation of the specific field instead of that of the table (or be sure that the field does use the table defaults).

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)

这篇关于MySQL排序顺序-排序规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！