相似匹配和比較之間的關係

我天真地假設 alike 'foo%'和 a之間的關係>= 'foo'是後者匹配前者和索引順序中後面的一些附加行。更一般地說，我認為對於給定的文本t，字元列上的索引分為四個區域，即

1. <( )之前的條目t，
2. 等於 ( =)的條目t，
3. 大於 ( >)的條目t仍具有t前綴 ( like concat(t, '%')) 和
4. 其餘的部分，

並且這些區域是連續的並且按順序排列。

此外，所有具有t前綴 ( like concat(t, '%')) 的行也在索引中形成一個連續區域，並且該區域在其開始時包含區域 2（這意味著它恰好是區域 2 和 3 一起）。

不過可能沒那麼簡單。例如，在 SQL Server中，排序規則中的單詞Häuser比較等於，並且兩者都是.Haeuser``German_PhoneBook_CI_AI``>= 'Ha'

但是，只有Haeusermatches like 'Ha%'，所以關於前綴的額外假設並不完全成立。

你可以在這個小提琴中看到這一點。

我的問題是這裡的其他假設可能是錯誤的。錨定的結果是否總是給出索引的連續切片，還是可能存在間隙？1我至少還有這四個連續的區域嗎？

我正在處理的潛在問題是一個通用數據庫 UI，它顯示給定搜尋詞的索引切片。從使用者體驗的角度來看，不讓使用者選擇是否要進行前綴、相等或大於搜尋是有益的，而是簡單地從相等開始（在升序搜尋的情況下），跟隨索引和然後標記兩個進一步的邊界“從這裡不再相等”和“這裡甚至不再是前綴”。

只有字元類型有這個問題，因為它來自排序規則的複雜性。

這個問題被標記為 SQL Server，但我覺得如果存在不正確的 RDMS，這可能是我需要了解的更深層次的問題 - 所以請隨意評論任何不同意我的假設的引擎。

---

1意思是，是否有價值觀r和wheres以及兩者都匹配錨定的喜歡但不匹配？r < t < s``r``s``t

編輯：

由於答案很好但有點正交，我想總結一下我在這裡學到的東西：

1. SQL Server 將 like 轉換為可以在查詢計劃中看到的索引範圍（但並非總是如此，我嘗試了韓語，然後 SQL Server 做了一些更棘手的事情來確定範圍邊界並且不會在計劃中逐字公開它們）。
2. 此範圍與錨定的點贊結果集不完全匹配，並且就範圍而言，結果集可能

• 甚至有縫隙，
• 甚至無法有效地使用索引。

例如，在 SQL Server 中，單詞 Häuser 在排序規則 German_PhoneBook_CI_AI 中位於 Haeuser 之前

不，它沒有。他們是平等的。

 select case when  N'Häuser' < N'Haeuser' Collate German_PhoneBook_CI_AI 
             then 'true' else 'false' end

返回

錯誤的

N'Hä' = N'Hae' Collate German_PhoneBook_CI_AI 

但

N'Ha' < N'Hä'  Collate German_PhoneBook_CI_AI 

所以N'Häuser'is not like 'Ha%'but is like N'Hae%'and like N'Hä%'。

SQL Server 可能無法like在索引搜尋+有序掃描中完全評估謂詞，而是必須搜尋+掃描更大範圍並將like運算符應用於更大範圍內的行。查看動態搜尋和隱藏的隱式轉換

這種行為是由於LIKE操作符/函式是如何擴展的。為了更清楚地看到這一點，我從您的 db<>fiddle 測試開始，並進行了一些更改和添加（如下所示）：

設置

-- DROP TABLE CollationTests;
create table CollationTests (
   i int identity,
   v varchar(20) collate German_PhoneBook_100_CI_AS not null, -- German_PhoneBook_CI_AI

   index IX_CollationTests_v nonclustered (v),
   constraint PK_CollationTests primary key clustered (i)
)


insert into CollationTests (v) values
('Hammer'),
('Hauser'),
('Häuser'),
('Haeuser'),
('Hae'),
('Hä'),
('Hc'),
('Horse'),
('Melon')
;

請注意原始 db<>fiddle 測試的以下更改：

• 桌子：

  • 將排序規則更改為German_PhoneBook_100_CI_AS
    • 使用 SQL Server > 2005 時，您確實應該使用 100 級排序規則（或在 SQL Server 2017+ 上使用 140 級排序規則）
    • 我切換到 (A)ccent (S)ensitive 以幫助說明ä==ae
  • 將聚集索引更改為“PK”而不是“IX”，以便在任何測試案例強制進行聚集索引掃描時更清楚

• 數據：

  • 我添加了最後五行以幫助使行為更清晰

測試

請注意：我在 SQL Server Management Studio (SSMS) 中執行了這些並啟用了“包括實際執行計劃       Ctrl+ M”

select * from CollationTests
where v = 'Ha' collate German_PhoneBook_100_CI_AI
order by v;
-- No rows

select * from CollationTests
where v = 'Hä'
order by v
/*
5   Hae
6   Hä
*/

select * from CollationTests
where v > 'Ha'
order by v
/*
5   Hae
6   Hä
3   Häuser
4   Haeuser
1   Hammer
2   Hauser
7   Hc
8   Horse
9   Melon
*/

select * from CollationTests where v like 'Ha%' order by v;
/* -- Seek Keys:
          Start: [dbo].[CollationTests].v >= Scalar Operator('Ha'),
            End: [dbo].[CollationTests].v <  Scalar Operator('HB')

5   Hae
4   Haeuser
1   Hammer
2   Hauser
*/

以下測試實際上顯示（通過“Seek Keys”，從執行計劃中獲取）ä被分解為ae（通過 Unicode 規範化）：

select * from CollationTests where v like 'Hä%' order by v;
/* -- Seek Keys:
          Start: [dbo].[CollationTests].v >= Scalar Operator('Hae'),
            End: [dbo].[CollationTests].v <  Scalar Operator('HaF')

5   Hae
6   Hä
3   Häuser
4   Haeuser
*/

以下測試使用“Seek Keys”，如上面的兩個測試所示：

select * from CollationTests
where v >= 'Ha'
and v < 'HB'
order by v
/* -- Seek Keys:
 Start: [dbo].[CollationTests].v >= Scalar Operator(CONVERT_IMPLICIT(varchar(8000),[@1],0))
   End: [dbo].[CollationTests].v < Scalar Operator(CONVERT_IMPLICIT(varchar(8000),[@2],0))

5   Hae
6   Hä
3   Häuser
4   Haeuser
1   Hammer
2   Hauser
*/

結果顯示返回了另外兩行，這可能表明當ä分解為 時存在時間差異ae，或者可能發生了一些事情，CONVERT_IMPLICIT()因為這不在使用LIKE運算符/函式的查詢的執行計劃中。

（似乎需要進行更多調查。我會看看我是否能想出一些東西，否則會在這裡註明，如果這是目前為止）

引用自：https://dba.stackexchange.com/questions/316446