我不了解整理? (MySQL，RDBMS，字符集) [英] I don't understand Collation? (Mysql, RDBMS, Character sets)

问题描述

我了解字符集，但我不了解排序规则.我知道您对Mysql或任何RDBMS中的每个字符集都获得了默认的排序规则，但是我还是不明白！有人可以用外行术语解释吗?

I Understand Character sets but I don't understand Collation. I know you get a default collation with every Character set in Mysql or any RDBMS but I still don't get it! Can someone please explain in layman terms?

提前谢谢;-)

推荐答案

数据库整理的要点是确定数据的排序和比较方式.

The main point of a database collation is determining how data is sorted and compared.

字符串比较的区分大小写

SELECT "New York" = "NEW YORK";` 

对于不区分大小写的归类，

将返回true；对于区分大小写的用户为false.

will return true for a case insensitive collation; false for a case sensitive one.

哪个排序规则可以通过排序规则名称中的_ci和_cs后缀来判断. _bin归类进行二进制比较(字符串必须100％相同).

Which collation does which can be told by the _ci and _cs suffix in the collation's name. _bin collations do binary comparisons (strings must be 100% identical).

变音符号/重音符号的比较

排序规则还确定在字符串比较中是否将重音字符视为其拉丁文基本对等物.

the collation also determines whether accented characters are treated as their latin base counterparts in string comparisons.

SELECT "Düsseldorf" =  "Dusseldorf";
SELECT "Èclair" =      "Eclair";

在前一种情况下将返回true；在后者中是错误的.您将需要阅读每个排序规则的描述，以找出哪个.

will return true in the former case; false in the latter. You will need to read each collation's description to find out which is which.

字符串排序

排序规则会影响字符串的排序方式.

The collation influences the way strings are sorted.

例如

• 单音符号Ä Ö Ü位于芬兰语/瑞典语字母latin1_swedish_ci

• Umlauts Ä Ö Ü are at the end of the alphabet in the finnish/swedish alphabet latin1_swedish_ci

在德国DIN-1分类(latin_german1_ci)中将它们视为A O U

they are treated as A O U in German DIN-1 sorting (latin_german1_ci)

以及在德国DIN-2分类(latin_german2_ci)中的AE OE UE. (电话簿"排序)

and as AE OE UE in German DIN-2 sorting (latin_german2_ci). ("phone book" sorting)

在latin1_spanish_ci中，ñ"(n-波浪号)是介于"n"和"o"之间的单独字母.

In latin1_spanish_ci, "ñ" (n-tilde) is a separate letter between "n" and "o".

使用非拉丁字符时，这些规则将导致不同的排序顺序.

These rules will result in different sort orders when non-latin characters are used.

在运行时使用归类

您必须为表和列选择排序规则，但是如果您不介意性能下降，则可以在运行时使用COLLATE关键字将数据库操作强制为某种排序规则.

You have to choose a collation for your table and columns, but if you don't mind the performance hit, you can force database operations into a certain collation at runtime using the COLLATE keyword.

这将使用德语DIN-2排序规则按name列对table进行排序:

This will sort table by the name column using German DIN-2 sorting rules:

SELECT name
FROM table
ORDER BY name COLLATE latin1_german2_ci;

在运行时使用COLLATE将对性能产生影响，因为在查询期间必须转换每一列.因此，在应用大型数据集之前，请三思而后行.

Using COLLATE at runtime will have performance implications, as each column has to be converted during the query. So think twice before applying this do large data sets.

MySQL参考:

• MySQL支持的字符集和排序规则
• 排序规则效果的示例
• 整理问题

• Character Sets and Collations That MySQL Supports
• Examples of the Effect of Collation
• Collation issues

这篇关于我不了解整理? (MySQL，RDBMS，字符集)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！