mysql的正确排序规则_MySQL排序顺序-排序规则？

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

and so is this:

Abc

Acc

Ábd

Looking forward to your response.

解决方案

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

UPD

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)