sql-server – 重音敏感排序

发布时间：2020-12-12 16:58:11 所属栏目：MsSql教程来源：网络整理

导读：为什么这两个SELECT语句导致不同的排序顺序？ USE tempdb;CREATE TABLE dbo.OddSort ( id INT IDENTITY(1,1) PRIMARY KEY,col1 NVARCHAR(2),col2 NVARCHAR(2));GOINSERT dbo.OddSort (col1,col2) VALUES (N'e',N'eA'),(N'é',N'éB'),(N'?',N'?C'),(N'è',N'

为什么这两个SELECT语句导致不同的排序顺序？

USE tempdb;
CREATE TABLE dbo.OddSort 
(
    id INT IDENTITY(1,1) PRIMARY KEY,col1 NVARCHAR(2),col2 NVARCHAR(2)
);
GO
INSERT dbo.OddSort (col1,col2) 
VALUES (N'e',N'eA'),(N'é',N'éB'),(N'?',N'?C'),(N'è',N'èD'),(N'ê',N'êE'),(N'ē',N'ēF');
GO

SELECT * 
FROM dbo.OddSort 
ORDER BY col1 COLLATE Latin1_General_100_CS_AS;

╔════╦══════╦══════╗
║ id ║ col1 ║ col2 ║
╠════╬══════╬══════╣
║  1 ║ e    ║ eA   ║
║  2 ║ é    ║ éB   ║
║  4 ║ è    ║ èD   ║  -- should be id 3?
║  5 ║ ê    ║ êE   ║
║  3 ║ ?    ║ ?C   ║
║  6 ║ ē    ║ ēF   ║
╚════╩══════╩══════╝

SELECT * 
FROM dbo.OddSort 
ORDER BY col2 COLLATE Latin1_General_100_CS_AS;

╔════╦══════╦══════╗
║ id ║ col1 ║ col2 ║
╠════╬══════╬══════╣
║  1 ║ e    ║ eA   ║
║  2 ║ é    ║ éB   ║
║  3 ║ ?    ║ ?C   ║
║  4 ║ è    ║ èD   ║
║  5 ║ ê    ║ êE   ║
║  6 ║ ē    ║ ēF   ║
╚════╩══════╩══════╝

解决方法

这个问题与数据库无关,而是与Unicode处理和规则有关.

基于https://docs.microsoft.com/en-us/sql/t-sql/statements/windows-collation-name-transact-sql Latin1_General_100_CS_AS表示：“排序使用Latin1常规字典排序规则并映射到代码页1252”,添加了CS =区分大小写和AS =重音敏感.

Windows代码页1252和Unicode(http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOWS/CP1252.TXT)之间的映射显示了我们正在处理的所有字符的相同值(除了使用macron在Microsoft映射中不存在的e,因此不知道它在这种情况下的作用),所以我们可以现在专注于Unicode工具和术语.

首先,让我们准确地知道我们正在处理的所有字符串：

0065  LATIN SMALL LETTER E
0041  LATIN CAPITAL LETTER A
00E9  LATIN SMALL LETTER E WITH ACUTE
0042  LATIN CAPITAL LETTER B
00EB  LATIN SMALL LETTER E WITH DIAERESIS
0043  LATIN CAPITAL LETTER C
00E8  LATIN SMALL LETTER E WITH GRAVE
0044  LATIN CAPITAL LETTER D
00EA  LATIN SMALL LETTER E WITH CIRCUMFLEX
0045  LATIN CAPITAL LETTER E
?0113  LATIN SMALL LETTER E WITH MACRON
0046  LATIN CAPITAL LETTER F

这里描述了Unicode校对算法：https://www.unicode.org/reports/tr10/

看看1.3节“语境敏感性”,它解释了排序不能仅依赖于一个字符,因为某些规则是上下文敏感的.

还要注意1.8中的这些要点：

Collation is not a property of strings.
Collation order is not preserved under concatenation or substring operations,in general.

By default,the algorithm makes use of three fully-customizable levels. For the Latin script,these levels correspond roughly to:

alphabetic ordering
diacritic ordering
case ordering.

但算法本身有点密集.它的要点是：简而言之,Unicode归类算法采用输入Unicode字符串和归类元素表,其中包含字符的映射数据.它产生一个排序键,它是一个无符号16位整数数组.然后可以对如此生成的两个或多个排序键进行二进制比较,以便在生成它们的字符串之间进行正确的比较.

您可以在此处查看特定的拉丁语排序规则：http://developer.mimer.com/collations/charts/latin.htm
或者更直接,更具体地用于MS SQL：
http://collation-charts.org/mssql/mssql.0409.1252.Latin1_General_CS_AS.html

对于e字符,它显示：

e E é é è è ê ê ? ?

这解释了在col1上订购时的结果,除了代码页1252中不存在ē,所以我完全不知道它用它做什么.

或者,如果我们手动执行Unicode算法,请在http://www.unicode.org/Public/UCA/latest/allkeys.txt使用DUCET的键值：

步骤1：规范化形式D,因此每个案例变为：

e => U+0065
é => U+0065 U+0301
? => U+0065 U+0308
è => U+0065 U+0300
ê => U+0065 U+0302
ē => U+0065 U+0304

步骤2,生成校对数组(在文件allkeys.txt中查找)

e => [.1D10.0020.0002]
é => [.1D10.0020.0002] [.0000.0024.0002]
? => [.1D10.0020.0002] [.0000.002B.0002]
è => [.1D10.0020.0002] [.0000.0025.0002]
ê => [.1D10.0020.0002] [.0000.0027.0002]
ē => [.1D10.0020.0002] [.0000.0032.0002]

步骤3,表单排序键(对于每个级别,取每个排序规则数组中的每个值,然后将0000作为分隔符再次开始下一级别)

e => 1D10 0000 0020 0000 0002
é => 1D10 0000 0020 0024 0000 0002 0002
? => 1D10 0000 0020 002B 0000 0002 0002
è => 1D10 0000 0020 0025 0000 0002 0002
ê => 1D10 0000 0020 0027 0000 0002 0002
ē => 1D10 0000 0020 0032 0000 0002 0002

步骤4,比较排序键(逐个对每个值进行简单的二进制比较)：
第四个值足以对它们进行排序,因此最终的顺序变为：

e
é
è
ê
?
ē

以同样的方式对col2进行排序：

第1步：NFD

eA => U+0065 U+0041
éB => U+0065 U+0301 U+0042
?C => U+0065 U+0308 U+0043
èD => U+0065 U+0300 U+0044
êE => U+0065 U+0302 U+0045
ēF => U+0065 U+0304 U+0046

第2步：归类数组

eA => [.1D10.0020.0002] [.1CAD.0020.0008]
éB => [.1D10.0020.0002] [.0000.0024.0002] [.1CC6.0020.0008]
?C => [.1D10.0020.0002] [.0000.002B.0002] [.1CE0.0020.0008]
èD => [.1D10.0020.0002] [.0000.0025.0002] [.1CF5.0020.0008]
êE => [.1D10.0020.0002] [.0000.0027.0002] [.1D10.0020.0008]
ēF => [.1D10.0020.0002] [.0000.0032.0002] [.1D4B.0020.0008]

第3步：表单排序键

eA => 1D10 1CAD 0000 0020 0020 0000 0002 0008
éB => 1D10 1CC6 0000 0020 0024 0020 0000 0002 0002 0008
?C => 1D10 1CE0 0000 0020 002B 0020 0000 0002 0002 0008
èD => 1D10 1CF5 0000 0020 0025 0020 0000 0002 0002 0008
êE => 1D10 1D10 0000 0020 0027 0020 0000 0002 0002 0008
ēF => 1D10 1D4B 0000 0020 0032 0020 0000 0002 0002 0008

第4步：比较排序键：
第二个值足以对它们进行排序,事实上它已经按递增顺序排列,所以最终的顺序确实如下：

eA
éB
?C
èD
êE
ēF

更新：添加Solomon Rutzky第三种情况,由于启用新规则的空间(我选择了“不可忽视的情况”),这种情况比较棘手：

第1步,NFD：

è 1 => U+0065 U+0300 U+0020 U+0031
ê 5 => U+0065 U+0302 U+0020 U+0035
e 2 => U+0065 U+0020 U+0032
é 4 => U+0065 U+0301 U+0020 U+0034
ē 3 => U+0065 U+0304 U+0020 U+0033
? 6 => U+0065 U+0308 U+0020 U+0036

第2步,生成归类数组：

è 1 => [.1D10.0020.0002] [.0000.0025.0002] [*0209.0020.0002] [.1CA4.0020.0002]
ê 5 => [.1D10.0020.0002] [.0000.0027.0002] [*0209.0020.0002] [.1CA8.0020.0002]
e 2 => [.1D10.0020.0002] [*0209.0020.0002] [.1CA5.0020.0002]
é 4 => [.1D10.0020.0002] [.0000.0024.0002] [*0209.0020.0002] [.1CA7.0020.0002]
ē 3 => [.1D10.0020.0002] [.0000.0032.0002] [*0209.0020.0002] [.1CA6.0020.0002]
? 6 => [.1D10.0020.0002] [.0000.002B.0002] [*0209.0020.0002] [.1CA9.0020.0002]

第3步,表单排序键：

è 1 => 1D10 0209 1CA4 0000 0020 0025 0020 0020 0000 0002 0002 0002 0002
ê 5 => 1D10 0209 1CA8 0000 0020 0027 0020 0020 0000 0002 0002 0002 0002
e 2 => 1D10 0209 1CA5 0000 0020 0020 0020 0000 0002 0002 0002
é 4 => 1D10 0209 1CA7 0000 0020 0024 0020 0020 0000 0002 0002 0002 0002
ē 3 => 1D10 0209 1CA6 0000 0020 0032 0020 0020 0000 0002 0002 0002 0002
? 6 => 1D10 0209 1CA9 0000 0020 002B 0020 0020 0000 0002 0002 0002 0002

第4步,比较排序键：

基本上第三个值决定了顺序,实际上它只是基于最后一个数字,所以顺序应该是：

è 1
e 2
ē 3
é 4
ê 5
? 6

第二次更新基于Solomon Rutzky关于Unicode版本的评论.

我目前使用了关于最新Unicode版本的allkeys.txt数据,即版本10.0

如果我们需要考虑Unicode 5.1,那将是：
http://www.unicode.org/Public/UCA/5.1.0/allkeys.txt

我刚检查过,对于上面的所有字符,整理数组都是
相反：

e => [.119D.0020.0002.0065]
é => [.119D.0020.0002.0065] [.0000.0032.0002.0301]
? => [.119D.0020.0002.0065] [.0000.0047.0002.0308]
è => [.119D.0020.0002.0065] [.0000.0035.0002.0300]
ê => [.119D.0020.0002.0065] [.0000.003C.0002.0302]
ē => [.119D.0020.0002.0065] [.0000.005B.0002.0304]

和：

eA => [.119D.0020.0002.0065] [.1141.0020.0008.0041]
éB => [.119D.0020.0002.0065] [.0000.0032.0002.0301] [.1157.0020.0008.0042]
?C => [.119D.0020.0002.0065] [.0000.0047.0002.0308] [.116F.0020.0008.0043]
èD => [.119D.0020.0002.0065] [.0000.0035.0002.0300] [.1182.0020.0008.0044]
êE => [.119D.0020.0002.0065] [.0000.003C.0002.0302] [.119D.0020.0008.0045]
ēF => [.119D.0020.0002.0065] [.0000.005B.0002.0304] [.11D5.0020.0008.0046]

和：

è 1 => [.119D.0020.0002.0065] [.0000.0035.0002.0300] [*0209.0020.0002.0020] [.1138.0020.0002.0031]
ê 5 => [.119D.0020.0002.0065] [.0000.003C.0002.0302] [*0209.0020.0002.0020] [.113C.0020.0002.0035]
e 2 => [.119D.0020.0002.0065] [*0209.0020.0002.0020] [.1139.0020.0002.0032]
é 4 => [.119D.0020.0002.0065] [.0000.0032.0002.0301] [*0209.0020.0002.0020] [.113B.0020.0002.0034]
ē 3 => [.119D.0020.0002.0065] [.0000.005B.0002.0304] [*0209.0020.0002.0020] [.113A.0020.0002.0033]
? 6 => [.119D.0020.0002.0065] [.0000.0047.0002.0308] [*0209.0020.0002.0020] [.113D.0020.0002.0036]

然后计算到以下排序键：

e => 119D 0000 0020 0000 0002 0000 0065
é => 119D 0000 0020 0032 0000 0002 0002 0000 0065 0301
? => 119D 0000 0020 0047 0000 0002 0002 0000 0065 0308
è => 119D 0000 0020 0035 0000 0002 0002 0000 0065 0300
ê => 119D 0000 0020 003C 0000 0002 0002 0000 0065 0302
ē => 119D 0000 0020 005B 0000 0002 0002 0000 0065 0304

和：

eA => 119D 1141 0000 0020 0020 0000 0002 0008 0000 0065 0041
éB => 119D 1157 0000 0020 0032 0020 0000 0002 0002 0008 0000 0065 0301 0042
?C => 119D 116F 0000 0020 0047 0020 0000 0002 0002 0008 0000 0065 0308 0043
èD => 119D 1182 0000 0020 0035 0020 0000 0002 0002 0008 0000 0065 0300 0044
êE => 119D 119D 0000 0020 003C 0020 0000 0002 0002 0008 0000 0065 0302 0045
ēF => 119D 11D5 0000 0020 005B 0020 0000 0002 0002 0008 0000 0065 0304 0046

和：

è 1 => 119D 0209 1138 0000 0020 0035 0020 0020 0000 0002 0002 0002 0002 0000 0065 0300 0020 0031
ê 5 => 119D 0209 113C 0000 0020 003C 0020 0020 0000 0002 0002 0002 0002 0000 0065 0302 0020 0035
e 2 => 119D 0209 1139 0000 0020 0020 0020 0000 0002 0002 0002 0000 0065 0020 0032
é 4 => 119D 0209 113B 0000 0020 0032 0020 0020 0000 0002 0002 0002 0002 0000 0065 0301 0020 0034
ē 3 => 119D 0209 113A 0000 0020 005B 0020 0020 0000 0002 0002 0002 0002 0000 0065 0304 0020 0033
? 6 => 119D 0209 113D 0000 0020 0047 0020 0020 0000 0002 0002 0002 0002 0000 0065 0308 0020 0036

这又给出了这三个排序结果：

e
é
è
ê
?
ē

和

eA
éB
?C
èD
êE
ēF

和

è 1
e 2
ē 3
é 4
ê 5
? 6

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!