Mysql按带有unicode字符的列排序 [英] Mysql order by on column with unicode characters

问题描述

我在mysql表上运行一个select查询，并试图通过表中的name列来订购。

name列包含英文字符名和带有拉丁字符的名称，如â。

我遇到以下问题。

我运行的查询返回按以下方式排序的结果，即

例如：如果Name包含archer ，aaakash，áhayden，bourne，jason

查询返回的结果排序如下

aaakash，archer但是，我想基于unicode代码点（如下）订购。

aaakash，bayne，bourne，jason ，archer，bourne，jason，hayden

（看到命令中hayden的位置的不同）
根据字符在unicode字符集中的位置，我可以如何排序结果？

解决方案

但是，我想根据unicode代码点（如下）订购

可能需要使用 utf8_bin 整理。

正确地， _bin 后缀表示按每个字符的二进制表示排序

。

---

>替换默认排序规则，您将使用 ORDER BY ... COLLATE ：

要解释文档：

  SELECT k 
 FROM t1 
 ORDER BY k COLLATE utf8_bin; 
   

utf8编码，您必须 CONVERT it：

  SELECT k 
 FROM t1 
 ORDER BY CONVERT（k USING utf8）COLLATE utf8_bin; 
  

_{请注意我使用了 utf8 这里是一个例子，因为这是最常见的Unicode编码。但您的MySQL伺服器可能支援其他Unicode编码，例如 ucs2 （UTF-16）。}

I am running a select query on mysql table and trying to order it by the "name" column in the table.
The name column contains both English character names and names with Latin character like â.
I am running into the below problem.
The query I run returns the results ordered in the below manner i.e.
Eg: if Name contains "archer", "aaakash", "â hayden", "bourne", "jason"
The results returned by the query is ordered as below
"aaakash", "archer", "â hayden", "bourne", "jason"

However I want to order it based on unicode code points (like below)
"aaakash", "archer", "bourne", "jason", "â hayden"

(See the difference in the position of â hayden in the orders)
What can I do to order the results based on the character's position in unicode character set?

解决方案

However I want to order it based on unicode code points (like below)

To sort using unicode code point, you probably need to use utf8_bin collation.

Precisely, the _bin suffix indicate to sort by the binary representation of each character.

---

To override the default collation while ordering, you will use ORDER BY ... COLLATE:

To paraphrase the documentation:

SELECT k
FROM t1
ORDER BY k COLLATE utf8_bin;

If your text column does not use utf8 encoding, you will have to CONVERT it:

SELECT k
FROM t1
ORDER BY CONVERT(k USING utf8) COLLATE utf8_bin;

_{Please notice I used utf8 as an example here as this is the most common Unicode encoding. But your MySQL server probably support other Unicode encoding, like ucs2("UTF-16").}

这篇关于Mysql按带有unicode字符的列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！