使用 COLLATE 的不區分大小寫的 ORDER BY 子句

我花了很長時間尋找這個，我收到的資訊不一。

COLLATE在其他 DBMS（在 SQLite、Oracle、MariaDB、MSSQL 中測試）中，我可以使用以下子句覆蓋預設排序順序：

SELECT *
FROM orderby
ORDER BY string COLLATE … ;

--  SQLite:     BINARY | NOCASE
--  MariaDB:    utf8mb4_bin | utf8mb4_general_ci
--  Oracle:     BINARY | BINARY_CI
--  MSSQL:      Latin1_General_BIN | Latin1_General_CI_AS

我仔細閱讀了文件並蒐索了高低，但我找不到任何對 PostgreSQL 如此簡單的東西。

是否有COLLATE可以對不區分大小寫進行排序的子句值？

我知道有很多關於區分大小寫的問題，但是（a）它們中的大多數都是舊的，並且（b）我所見過的都與該COLLATE條款無關。

FWIW，我正在 PostgreSQL 11.8 上進行測試。我在http://sqlfiddle.com/#!17/05cab/1上有一個測試小提琴，但它僅適用於 PostgreSQL 9.6。

MySQL/MariaDB 和 SQL Server 預設不區分大小寫，這在對大多數文本進行排序時肯定是有意義的。Oracle 和 SQLite 預設區分大小寫，但是對於不區分大小寫的排序有一個相對簡單的解決方案。我的數據庫的預設排序規則是en_US.UTF-8. 我試圖在這裡填補一些空白。

如果您使用的是 PostgreSQL 12 或更新版本，您將能夠通過CREATE COLLATION命令創建一個新的排序規則，該命令指定deterministic = false：

CREATE COLLATION ci (provider = icu, locale = 'en-US-u-ks-level2', deterministic = false);

我沒有使用 sqlfiddle，而是使用了 db<>fiddle，它提供了幾個版本的 PostgreSQL，例如：

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a8f4b330d04266947fb11e5c04fa4891&hide=2

此解決方案類似於適用於此相關答案的解決方案（也在 DBA.SE 上）：

LIKE 不支持 PostgreSQL nondeterministic collat​​ions

帶有ORDER BY子句的前四個 db<>fiddle 範例查詢顯示了三種不同的排序結果：

1. 查詢返回更接近二進制/序數排序的ORDER BY "string" COLLATE "POSIX"行，所有大寫字母 (A - Z) 在所有小寫字母 (a - z) 之前分組。
2. ORDER BY "string"和查詢以ORDER BY "string" COLLATE "en_US.utf8"有效區分大小寫的順序返回行，其中“A”和“a”組合在一起，每個字母的小寫字母位於同一字母的大寫字母之前（aA bB …）。
3. 該ORDER BY "string" COLLATE "ci"查詢以真正不區分大小寫的順序返回行，其中“A”和“a”組合在一起，但這次每個特定字母的大小寫不是按預定義的順序，這兩種大小寫甚至可以在一個字母中混合使用。由於不能保證每次都生成相同的訂單，因此這裡是我目前看到的此查詢的結果集範例：

-8    banana
-15   Banana
-9    bANana
1116  Banana

版本12的整理支持文件中的以下聲明解釋了需要設置為：deterministic``false

請注意，雖然此系統允許創建“忽略大小寫”或“忽略重音”或類似的排序規則（使用 ks 鍵），但為了使此類排序規則以真正不區分大小寫或重音的方式起作用，它們還需要在;… 中聲明為不確定CREATE COLLATION的，否則，任何根據排序規則比較相等但不是按字節相等的字元串都將根據它們的字節值進行排序。

但是來自版本11的同一個排序規則支持文件的相同註釋解釋說排序仍然有效區分大小寫：

請注意，雖然該系統允許創建“忽略大小寫”或“忽略重音”或類似的排序規則（使用 ks 鍵），但 PostgreSQL 目前不允許此類排序規則以真正不區分大小寫或重音的方式執行。根據排序規則比較相等但字節不相等的任何字元串都將根據它們的字節值進行排序。

仔細查看CREATE COLLATION文件，似乎在Version 12deterministic中引入了該屬性（預設為true但需要設置為false進行不敏感的比較和排序），這並沒有真正幫助您。（@LaurenzAlbe 在評論中推薦了一種替代語法，該語法可能適用於舊版本的 libicu，並針對該語法調整上面連結的 db<>fiddle 測試並在 PostgreSQL 11 上執行，它似乎不起作用：https:// dbfiddle.uk/?rdbms=postgres_11&fiddle=c3c48e111ed1837987524fee2c54a183&hide=2 )

這讓您有兩個選擇：

1. lower()功能
2. citext數據類型（如@a_horse_with_no_name 在對問題的評論中提到的）

數據類型在內部citext呼叫lower()函式，但具有以下好處（根據文件，上面直接連結）：

1. 它適用於由UNIQUE和PRIMARY KEY約束創建的隱式索引
2. 不需要記住添加lower()到比較的兩邊

兩個主要考慮因素是：

• citext效率不如text運算符函式和 B-tree 比較函式必須複製數據並將其轉換為小寫進行比較。然而，它比使用 lower 來獲得不區分大小寫的匹配要稍微高效一些。
• citext如果您需要數據在某些情況下區分大小寫而在其他情況下不區分大小寫，則無濟於事。標準答案是在text需要不區分大小寫比較時使用 type 並手動使用 lower 函式；如果不經常需要不區分大小寫的比較，這一切正常。如果您大部分時間需要不區分大小寫的行為並且不經常區分大小寫，請考慮將數據儲存為citext並在需要區分大小寫比較時將列顯式轉換為文本。在任何一種情況下，如果您希望兩種類型的搜尋都快速，您將需要兩個索引。

這聽起來像是citext創建了一個佔用更多空間但由於預先計算而速度稍快的數據的小寫副本。

另外，citext需要安裝。我修改了前面的範例查詢以citext顯示可以在 PostgreSQL 11 上執行的操作，但似乎citext沒有安裝在 db<>fiddle 上。我會提出這個要求。但這裡是測試查詢，如果您已citext安裝，應該顯示它在您的系統上的工作方式：

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=7dd95430aad8f646bfa1b7e79e21871c&hide=2

綜上所述，重要的是要注意，對於唯一或最右邊/最後一列，不區分大小寫的**排序與區分大小寫的排序沒有太大區別：兩種情況下的相同字母將組合在一起. 只是在區分大小寫的排序下，每個字母中的每個大小寫都會組合在一起（即所有“a”之前或之後的所有“A”），而不區分大小寫的排序允許“A”和“a”混合（因為兩者都沒有優先級）。允許同一個字母的不同大小寫混合變得很重要，當至少有一個額外的排序標准在ORDER BY列表，指定在相關字元串列的右側（即之後）。此答案頂部連結的 db<>fiddle 中的最後兩個範例查詢顯示了差異。

考慮到所有這些，主要問題是：您到底想要完成什麼？正如問題中目前所述，該請求是針對區分大小寫的排序。這是否意味著比較仍應區分大小寫？這似乎是一個奇怪的組合，因為有/想要一個區分大小寫的比較是相當普遍**的，但排序不太重要，因為區分大小寫和不區分大小寫的排序之間通常幾乎沒有區別（區分大小寫是一致的，但案例敏感將具有僅在大小寫不同的字元串的隨機排序，並且隨機性有時會導致與區分大小寫返回的排序相同），除非有其他欄位正在排序。

來自OP的評論：

MySQL/MariaDB 和 SQL Server 預設不區分大小寫，這在對大多數文本進行排序時肯定是有意義的。Oracle 和 SQLite 預設區分大小寫，但是對於不區分大小寫的排序有一個相對簡單的解決方案。我的數據庫的預設排序規則是en_US.UTF-8. 我試圖在這裡填補一些空白。

目前尚不清楚為什麼需要填補任何“空白”，但聽起來術語有一個小問題：正在使用術語“排序”/“排序”，但我懷疑主要問題是比較，考慮到在排序時區分大小寫和不區分大小寫之間通常沒有明顯的區別。並且，在不使用子句覆蓋謂詞或ORDER BY欄位/表達式的情況下COLLATE，相同的敏感性（或缺乏敏感性）將適用於兩者。這很重要，因為如果我是正確的，OP 確實意味著兩者，那麼：

1. 在性能方面，我猜這lower實際上可能比citext 在這個特定的上下文中強制轉換稍微好一些，因為無論如何citext都會呼叫lower但也會進行數據類型轉換。公平地說，我沒有對此進行任何測試，所以這只是一個有根據的猜測，假設當用作列的數據類型時，citext由於預先計算的值會稍微好一些，但在這種情況下沒有機會預先計算。
2. 使用其中一個citext或lower其中一個ORDER BY只會解決一半的問題（並且給出的不太重要的一半，同樣，區別幾乎不明顯）
3. 很可能您需要停止關注ORDER BY子句並citext用作列的數據類型，這將同時處理比較和排序。

另一個建議是你使用GENERATED COLUMNS如下。

我確實知道這可能會浪費空間，但是一旦 PostgreSQL 實現了 VIRTUAL 儲存機制GENERATED COLUMNS，這不應該是（太多的）問題。對於這個問題，美中不足的是它們僅適用於 PostgreSQL 版本 >= 12 - 這更像是一個潛在的建議。

從好的方面來說，GENERATED COLUMN 解決方案的優點是不需要擴展 - 通常在小提琴中是不可能的，但更重要的是，作為顧問，通常不允許安裝擴展和其他軟體。

所以，我做了以下（見小提琴here）：

CREATE TABLE test
(
 num SERIAL PRIMARY KEY,
 the_string TEXT NOT NULL,
 the_string_ci TEXT GENERATED ALWAYS AS (LOWER (the_string)) STORED
);

填充它：

INSERT INTO test (the_string)
VALUES
('Apple'), ('apple'), 
('Orange'), ('orange'), 
('Banana'), ('banana'), 
('Pear'), ('pear');

然後執行此查詢 - 區分大小寫：

SELECT * FROM test WHERE the_string = 'Apple';

結果：

num the_string  the_string_ci
 1      Apple          apple

以及不區分大小寫的查詢：

SELECT * FROM test WHERE the_string_ci = 'apple';

結果：

num the_string  the_string_ci
 1      Apple          apple
 2      apple          apple

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