MySQL 整理是什么意思？

What does collation mean?

提问by LOVE_KING

What does collation mean in SQL, and what does it do?

SQL 中的排序规则是什么意思，它有什么作用？

回答by paxdiablo

Collation can be simply thought of as sort order.

整理可以简单地认为是排序顺序。

In English (and it's strange cousin, American), collation may be a pretty simple matter consisting of ordering by the ASCII code.

在英语中（它是奇怪的堂兄，美国人），校对可能是一个非常简单的事情，包括按 ASCII 代码排序。

Once you get into those strange European languages with all their accents and other features, collation changes. For example, though the different accented forms of amay exist at disparate code points, they may all need to be sorted as if they were the same letter.

一旦你进入那些带有所有口音和其他特征的奇怪的欧洲语言，排序规则就会改变。例如，虽然不同重音形式的a可能存在于不同的代码点，但它们可能都需要排序，就好像它们是同一个字母一样。

回答by Joe Pineda

Besides the "accented letters are sorted differently than unaccented ones" in some Western European languages, you must take into account the groupsof letters, which sometimes are sorted differently, also.

除了在某些西欧语言中“重音字母的排序与非重音字母的排序不同”之外，您还必须考虑字母组，这些字母有时也有不同的排序方式。

Traditionally, in Spanish, "ch" was considered a letter in its own right, same with "ll" (both of which represent a single phoneme), so a list would get sorted like this:

传统上，在西班牙语中，“ch”本身被认为是一个字母，与“ll”相同（两者都代表一个音素），因此列表将按如下方式排序：

• caballo
• cinco
• coche
• charco
• chocolate
• chueco
• dado
• (...)
• lámpara
• luego
• llanta
• lluvia
• madera

• 卡瓦洛
• 辛科
• 科什
• 查科
• 巧克力
• 丘埃科
• 墙裙
• （……）
• 兰帕拉
• 卢戈
• 扬塔
• 露维亚
• 马德拉

Notice all the words starting with single cgo together, except words starting with chwhich go afterthem, same with ll-starting words which go after all the words starting with a single l. This is the ordering you'll see in old dictionaries and encyclopedias, sometimes even today by very conservative organizations.

通知所有启动单的话Ç一起去，除了开头的词语CH哪去了后他们同样与LL-starting话毕竟开始一个字里面去升。这是您在旧词典和百科全书中看到的排序方式，有时甚至在今天也是非常保守的组织。

The Royal Academy of the Language changed this to make it easier for Spanish to be accomodated in the computing world. Nevertheless, ?is still considered a different letter than nand goes afterit, and before o. So this is a correctly ordered list:

皇家语言学院改变了这一点，使西班牙语更容易融入计算世界。尽管如此，? 仍然被认为是与n不同的字母，并在它之后，在o之前。所以这是一个正确排序的列表：

• Namibia
• número
• ?andú
• ?ú
• obra
• ojo

• 纳米比亚
• 努梅罗
• ？和你
• ?ú
• 奥布拉
• 大城

By selecting the correct collation, you get all this done for you, automatically :-)

通过选择正确的排序规则，您可以自动完成所有这些工作:-)

回答by Quassnoi

Rules that tell how to compare and sort strings: letters order; whether case matters, whether diacritics matter etc.

说明如何比较和排序字符串的规则：字母顺序；大小写是否重要，变音符号是否重要等。

For instance, if you want all letters to be different (say, if you store filenames in UNIX), you use UTF8_BINcollation:

例如，如果您希望所有字母都不同（例如，如果您将文件名存储在 中UNIX），您可以使用UTF8_BIN排序规则：

SELECT  'A' COLLATE UTF8_BIN = 'a' COLLATE UTF8_BIN

---
0

If you want to ignore case and diacritics differences (say, for a search engine), you use UTF8_GENERAL_CIcollation:

如果您想忽略大小写和变音符号的差异（例如，对于搜索引擎），您可以使用UTF8_GENERAL_CI排序规则：

SELECT  'A' COLLATE UTF8_GENERAL_CI = '?' COLLATE UTF8_GENERAL_CI

---
1

As you can see, this collation (comparison rule) considers capital Aand lowecase ?the same letter, ignoring case and diacritic differences.

如您所见，此排序规则（比较规则）将大写A和小写视为?相同的字母，忽略大小写和变音符号差异。

回答by gbn

Collation defines how you sort and compare string values

排序规则定义了如何对字符串值进行排序和比较

For example, it defines how to deal with

例如，它定义了如何处理

• accents (?àaetc)
• case (Aa)
• the language context:
  • In a French collation, cote < c?te < coté < c?té.
  • In the SQL Server Latin1 default , cote < coté < c?te < c?té
• ASCII sorts (a binary collation)

• 口音（?àa等）
• 案例 ( Aa)
• 语言环境：
  • 在法语整理中， cote < c?te < coté < c?té.
  • 在 SQL Server Latin1 默认情况下， cote < coté < c?te < c?té
• ASCII 排序（二进制排序规则）

回答by Murali Mohan

Collation means assigning some order to the characters in an Alphabet, say, ASCII or Unicode etc.

整理意味着为字母表中的字符分配某种顺序，例如 ASCII 或 Unicode 等。

Suppose you have 3 characters in your alphabet - {A,B,C}. You can define some example collations for it by assigning integral values to the characters

假设您的字母表中有 3 个字符 - {A,B,C}。您可以通过为字符分配整数值来为其定义一些示例排序规则

1. Example 1 = {A=1,B=2,C=3}
2. Example 2 = {C=1,B=2,A=3}
3. Example 3 = {B=1,C=2,A=3}

1. 示例 1 = {A=1,B=2,C=3}
2. 示例 2 = {C=1,B=2,A=3}
3. 示例 3 = {B=1,C=2,A=3}

As a matter of fact, you can define n! collations on an Alphabet of size n. Given such an order, different sorting routines likes LSD/MSD string sorts make use of it for sorting strings.

事实上，你可以定义 n! 大小为 n 的 Alphabet 上的排序规则。给定这样的顺序，不同的排序例程（如 LSD/MSD 字符串排序）利用它对字符串进行排序。

回答by Anvesh

Reference is taken from this Article:A collation is a set of rules for comparing characters in a character set. It has also ruled for sorting of characters and proper order of two characters varies from language to language. A Collation compared two strings like, if a word is greater than another one, and sort accordingly.

参考自这篇文章：排序规则是一组用于比较字符集中字符的规则。它还规定了字符的排序，并且两个字符的正确顺序因语言而异。排序规则比较两个字符串，例如，如果一个词大于另一个词，并相应地排序。

If you are using “latin1” Character set, you can use “latin1_swedish_ci” Collation.

如果您使用“latin1”字符集，则可以使用“latin1_swedish_ci”排序规则。

You have to choose right collation because wrong collation may affect your database performance.

您必须选择正确的排序规则，因为错误的排序规则可能会影响您的数据库性能。

回答by Dr G

Collation determines how your data is sorted and compared. It's very often important with regards to internazionalization, e.g. how do you sort japanese kanji?

整理决定了数据的排序和比较方式。就国际化而言，这通常很重要，例如，您如何对日本汉字进行排序？

If you google collation and sql server you'll find plenty of articles discussing it!

如果您使用谷歌整理和 sql server，您会发现很多讨论它的文章！

回答by S.Lott

http://en.wikipedia.org/wiki/Collation

http://en.wikipedia.org/wiki/Collat​​ion

Collation is the assembly of written information into a standard order. (...) A collation algorithm such as the Unicode collation algorithmdefines an order through the process of comparing two given character strings and deciding which should come before the other.

整理是将书面信息组装成标准顺序。(...) 诸如Unicode整理算法之类的整理算法通过比较两个给定字符串并决定哪个应该在另一个之前的过程来定义顺序。

回答by Oded

The collation is how SQL server decides on how to sort and compare text.

排序规则是 SQL Server 决定如何排序和比较文本的方式。

See MSDN.

请参阅MSDN。