sql-server - 使用区分大小写的排序规则时，为什么对范围进行过滤会匹配错误的大小写？

Question

带有排序规则的 SQL Server 标准 64 位 SQL_Latin1_General_CP1_CS_AS

表请:

ort varchar(30)    SQL_Latin1_General_CP1_CS_AS

select  ort,
from plz
where
   ort >= 'zürich' 
   and ort <= 'zürichz'

选择此数据:

Zürich
Zürich Mülligen
Zürich 80

如果第二个 zürich 末尾没有 z，则不会选择任何数据，这是可以的。但为什么它在区分大小写的服务器上显示数据？

Answer 1

这里发生了两个困惑:

• 相等与排序

  使用相等运算符时=区分大小写的问题更加直接和明显(例如“z”<>“Z”)。但是当使用>时和<运算符排序是必需的，这与区分大小写和不区分大小写是分开的，尽管受其影响。排序首先由排序类型决定(参见下一点)，然后可能由区分大小写决定。

• 字典顺序与二进制(即 ASCII 值/代码点)顺序

  默认情况下，字典排序意味着将使用特定语言的字母顺序。特定语言是排序规则名称的一部分，例如 Latin1_General或Danish 。这会将“a”(ASCII = 97) 和“A”(ASCII = 65) 放在一起，并将“Z”(ASCII = 90) 放在两者后面。二进制排序规则(即以 _BIN 或 _BIN2 结尾的排序规则)将使用二进制字符值，它将“Z”放在“A”和“a”之间。二进制排序必然区分大小写，因为它对所有内容都敏感。字典排序会将“A”和“a”分组在一起，但如果排序规则也区分大小写，则只会强制“A”位于“a”之前。不区分大小写的字典顺序可以混合“A”和“a”值(请参阅示例中的最终 SELECT)。

现在让我们将这一切与问题联系起来。当前排序规则是SQL_Latin1_General_CP1_CS_AS这是一个字典排序。当查字典时，Zürich 和 zürich 都会被连续找到； Zürich 不会仅仅因为它的大写字母 Z 而出现在 zany 之前。

查看查询返回的结果，第一个条目 Zürich 不应该出现。我无法将其退回。由于这是区分大小写的排序规则，Zürich 确实位于 zürich 之前，并且会被 WHERE 过滤掉。条款。但其他两个值 Zürich Mülligen 和 Zürich 80 可以有效返回，因为它们与 WHERE 匹配。条款。如果您希望它们不匹配，那么您需要使用二进制排序规则而不是区分大小写的字典排序规则。

要查看实际效果，请运行以下命令:

字典(区分大小写):

SELECT tmp.val
FROM (SELECT 'bat'
      UNION ALL
      SELECT 'bar'
      UNION ALL
      SELECT 'bad'
      UNION ALL
      SELECT 'Bar') tmp(val)
ORDER BY tmp.val COLLATE SQL_Latin1_General_CP1_CI_AS ASC;

结果:

bad
bar
Bar -- "Bar" typically, but not necessarily, comes after "bar"
bat

字典(区分大小写):

SELECT tmp.val
FROM (SELECT 'bat'
      UNION ALL
      SELECT 'bar'
      UNION ALL
      SELECT 'bad'
      UNION ALL
      SELECT 'Bar') tmp(val)
ORDER BY tmp.val COLLATE SQL_Latin1_General_CP1_CS_AS ASC;

结果:

bad
Bar -- "Bar" necessarily comes before "bar", but not before "bad, or even "b"
bar
bat

二进制:

SELECT tmp.val
FROM (SELECT 'bat'
      UNION ALL
      SELECT 'bar'
      UNION ALL
      SELECT 'bad'
      UNION ALL
      SELECT 'Bar') tmp(val)
ORDER BY tmp.val COLLATE Latin1_General_BIN2 ASC;

结果:

Bar -- "Bar" ("B" value = 66) necessarily comes before "b" (value = 98)
bad
bar
bat

---

为了更好地了解区分大小写如何影响一般的过滤和排序，请运行以下分步示例。

DECLARE @test TABLE (string VARCHAR(30)
                         COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL);
INSERT INTO @test (string) values ('A');
INSERT INTO @test (string) values ('B');
INSERT INTO @test (string) values ('Y');
INSERT INTO @test (string) values ('Z');
INSERT INTO @test (string) values ('a');
INSERT INTO @test (string) values ('b');
INSERT INTO @test (string) values ('y');
INSERT INTO @test (string) values ('z');

-- case-sensitive, dictionary ordered sorting via column collation
SELECT [string] AS [Test1] FROM @test ORDER BY string ASC;
/* -- upper-case and lower-case of each letter are grouped together;
   -- upper-case consistently comes first due to case-sensitive
A
a
B
b
Y
y
Z
z
*/

-- case-sensitive comparison via column collation
SELECT [string] AS [Test2] FROM @test WHERE string = 'Y';
/* -- equality is straight-forward when using case-sensitive comparison
Y
*/

-- case-sensitive, dictionary ordered comparison and sorting via column collation
SELECT [string] AS [Test3] FROM @test WHERE string >= 'Y' ORDER BY string ASC;
/*  -- upper-case comes first due to case-sensitive
Y
y
Z
z
*/

-- case-sensitive, dictionary ordered comparison and sorting via column collation
SELECT [string] AS [Test4] FROM @test WHERE string >= 'y' ORDER BY string ASC;
/* -- upper-case comes first due to case-sensitive
y
Z
z
*/

-- case-insensitive, dictionary ordered comparison via collation override
SELECT [string] AS [Test5] FROM @test
WHERE string > 'Y' COLLATE SQL_Latin1_General_CP1_CI_AS;
/* -- upper-case and lower-case are the same due to case-INsensitive
Z
z
*/

-- binary ordering via collation override
SELECT [string] AS [Test6] FROM @test ORDER BY string COLLATE Latin1_General_BIN ASC;
/*
A
B
Y
Z
a
b
y
z
*/

-- case-sensitive, dictionary ordered comparison via column collation;
-- binary ordering via collation override
SELECT [string] AS [Test7] FROM @test WHERE string >= 'y'
   ORDER BY string COLLATE Latin1_General_BIN ASC;
/* -- lower-case 'y' comes before both 'Z' and 'z' when using a dictionary comparison
Z
y
z
*/

-- binary comparison via collation override;
-- binary ordering via collation override
SELECT [string] AS [Test8] FROM @test WHERE string >= 'y' COLLATE Latin1_General_BIN
   ORDER BY string COLLATE Latin1_General_BIN ASC;
/* -- lower-case 'y' comes after all capitals when using a binary comparison
y
z
*/

-- case-insensitive, dictionary ordered sorting via collation override
SELECT [string] AS [Test9] FROM @test
ORDER BY string COLLATE SQL_Latin1_General_CP1_CI_AS ASC;
/* -- upper-case and lower-case of each letter are grouped together,
   -- but inconsistent for upper-case vs lower-case first
A
a
b
B
Y
y
z
Z
*/

有关一些 SQL Server 排序规则信息，请参阅以下链接:

• SQL Server Collation Name
• Selecting a SQL Server Collation