sql-server – 重音敏感排序

MsSQL 前端之家
前端之家收集整理的这篇文章主要介绍了sql-server – 重音敏感排序，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
为什么这两个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
原文链接：https://www.f2er.com/mssql/80610.html

猜你在找的MsSQL相关文章

SQL Server2012高可用之日志传送测试
（一）日志传送架构 （1.1）相关服务器 主服务器 ：用于生产的服务器，上面运行这生产SQL ...
SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制（发布订阅）后，生产数据...
SQL Server2012高可用之事物复制(发布订阅)测试
（一）测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式，其中主库可执行读写...
使用zabbix监控sql server的发布订阅
（一）背景个人在使用sql server时，用到了sql server的发布订阅来做主从同步，类似MySQL的...
SQL学习（四）集合运算
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * fro...
SQL学习（五） 高级处理
5.1 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。 SEL...
SQL学习(一）初识数据库
datawhale组队学习SQL task01
SQL学习(二）基础查询与排序
datawhale数据库组队学习第二天打卡
SQL Server 2005 返回修改后的数据
今天在公司看到同事写了个SQL2005的新特性的文章，觉得很实用，在这里和大家分享下。原著作...
SQL Server 数据库最小宕机迁移方案
一、目的 在做SQL Server数据库维护的时候，当上司要求我们把几十G的数据文件搬动到其它服...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问