22.2. 排序規則支持 · PostgreSQL 中文文檔 9.3

# 22.2\. 排序規則支持排序規則特性允許為每一列數據指定排序順序和字符分類行為，或者甚至為每個操作指定。這緩解了`LC_COLLATE`和`LC_CTYPE`在數據庫被創建后不能被修改的限制。 ## 22.2.1\. 概念從概念上講，collatable數據類型的每個表達式都有一個排序規則。（內置collatable數據類型有`text`, `varchar`以及`char`。用戶定義的基本類型，也可以標記為collatable，當然一個collatable數據類型的域也是collatable的）。如果表達式是一個列引用，該表達式的排序規則是就這個列的排序規則。如果表達式是一個常數，排序規則是常數數據類型的缺省排序規則。一個更復雜表達式的排序規則從它的輸入端排序規則推導，如下所述。一個表達式的排序規則可以是"缺省"排序規則，這意味著為數據庫的區域設置。它也可以用于表達式的排序規則是不確定的。在這種情況下，排序操作符以及其他需要知道排序規則的操作符會在執行時失敗。當數據庫系統必須執行排序或字符分類時，它使用輸入表達式的排序規則。這種情況發生，例如，使用`ORDER BY`子句以及函數或運算符調用比如`<`。應用到`ORDER BY`子句中的排序規則直接就是排序關鍵字的排序規則。應用到函數或操作符調用的排序規則要從參數上派生，如下文所述。除了比較操作符外，大小寫字母轉換函數，如`lower`, `upper`和 `initcap`；模式匹配運算符號；以及 `to_char`及其相關函數都需要考慮排序規則。對于一個函數或運算符調用，通過檢查用來在運行時執行指定操作的參數排序規則派生出該排序規則。如果函數或運算符調用結果是collatable數據類型，并且有需要知道該排序規則的外圍表達式，排序規則也可用于在解析時作為函數或運算符表達式的定義的排序規則。表達式的_排序規則推導_可以是隱式或顯式的。當多個不同的排序規則出現在表達式中，這種區別會影響排序規則如何組合，當使用`COLLATE`子句的時候，產生顯式排序規則推導;所有其他排序規則推導是隱式的。當多個排序規則必須結合時，例如在一個函數調用中，使用下面的規則： 1. 如果任何輸入表達式有顯式的排序規則推導，那么輸入表達式中所有顯式派生的排序規則必須是一樣的，否則將引發錯誤。如果有任何顯式派生的排序規則，這即是排序規則組合的結果。 2. 否則，所有輸入表達式必須具有相同的隱式排序規則推導或者缺省排序規則。如果出現任何非缺省的排序規則，則是這就是排序組合的結果。否則，結果是缺省排序規則。 3. 如果在輸入的表達式之間非缺省的隱式排序規則有沖突，那么該組合視為不明確的排序規則。這不是一個錯誤情況，除非被調用的那個函數需要知道排序規則。如果是這樣，在運行時將引發一個錯誤。比如，考慮這個表定義： ``` CREATE TABLE test1 ( a text COLLATE "de_DE", b text COLLATE "es_ES", ... ); ``` 然后 ``` SELECT a < 'foo' FROM test1; ``` 按照`de_DE`規則，執行`<`比較，因為表達式組合隱式推導排序規則與缺省排序規則，但是 ``` SELECT a < ('foo' COLLATE "fr_FR") FROM test1; ``` 使用`fr_FR`規則執行比較，因為顯式排序規則覆蓋了隱式的。此外， ``` SELECT a < b FROM test1; ``` 解析器無法確定應該應用哪個排序規則，因為 `a`列和`b`列擁有沖突的隱式排序規則。因為`<`操作符確實需要知道所使用的排序規則，這將導致一個錯誤。錯誤可以通過附加一個明確的排序規則說明符給輸入表達式得以解決，如下： ``` SELECT a < b COLLATE "de_DE" FROM test1; ``` 或者等效的， ``` SELECT a COLLATE "de_DE" < b FROM test1; ``` 另一方面，結構上類似的情況 ``` SELECT a || b FROM test1; ``` 不會產生錯誤，因為`||`操作符并不關心排序規則：不論什么排序規則結果都是一樣的。如果函數或者操作符的結果還是collatable數據類型, 那么分配給函數或者操作符的組合的輸入表達式的排序規則也會應用到函數或者操作符結果上。因此， ``` SELECT * FROM test1 ORDER BY a || 'foo'; ``` 按照`de_DE`規則執行該排序。但是這個查詢： ``` SELECT * FROM test1 ORDER BY a || b; ``` 導致一個錯誤，因為即使`||`操作符不需要知道排序規則，而`ORDER BY`子句確實需要。和前面一樣，可以使用顯式的排序規則說明符解決這個沖突。 ``` SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR"; ``` ## 22.2.2\. 管理排序規則排序規則是把SQL名稱映射到操作系統區域的SQL模式對象。特別是，它映射到`LC_COLLATE`和`LC_CTYPE`的組合。（顧名思義，排序規則的主要目的是設置`LC_COLLATE`，它控制排序順序。但在實際中很少需要一個和 `LC_COLLATE`不同的`LC_CTYPE`設置，所以將它們合在一起，而不是為每個表達式另外創造一個設置 `LC_CTYPE`的基礎設施，更為方便）。另外，排序規則和字符集編碼（參見[Section 22.3](#calibre_link-1272)）緊密關聯。對不同的編碼可能存在同名的排序規則。在所有平臺上，名稱為`default`, `C`和`POSIX`排序規則都是可用的。其他可用的排序規則取決于操作系統支持。`default`排序規則選擇在創建數據庫時指定的`LC_COLLATE` 和`LC_CTYPE`值。 `C`和`POSIX`的排序規則都表現為"傳統 C"的行為，即只有ASCII字母"`A`"到"`Z`" 被視為字母，并且嚴格按照字符的編碼字節值進行排序。如果操作系統提供了在一個程序中使用多語言環境的支持 (`newlocale`以及相關函數)，那么當一個數據庫集群初始化的時候， `initdb`使用基于當時在操作系統上發現的所有語言環境的排序規則來填充系統表`pg_collation`。例如，操作系統可能提供名為`de_DE.utf8`的區域。那么，`initdb`就可能為編碼`UTF8`創建命名為 `de_DE.utf8`的排序規則，它的`LC_COLLATE`和 `LC_CTYPE`都被設置為`de_DE.utf8`。它還將創建名稱中被剝離了`.utf8`標簽的排序規則。所以，你也可以使用`de_DE`名稱的排序規則，這方便于編寫并且使得名稱較少依賴于編碼。然而，需要注意的是，排序規則名稱的初始設置是平臺相關的。在需要有不同的`LC_COLLATE`和`LC_CTYPE` 值的排序規則的情況下，可以使用[CREATE COLLATION](#calibre_link-559) 命令創建新的排序規則。該命令也可以從現有排序規則中創建一個新的，這可能是有用的，以便能夠在應用中使用操作系統無關的排序規則名稱。在任何特定的數據庫中，只關心使用該數據庫編碼的排序規則。 `pg_collation`中的其他項目會被忽略。因此，剝離了編碼名的排序規則名稱，如`de_DE`，在一個給定的數據庫中也算是獨一無二的，即使它不是全局唯一的。建議使用剝離過的排序規則名稱，因為如果你以后決定改變到另一個數據庫編碼，可以少改變一樣東西。但是請注意，無論是什么數據庫編碼，都可以使用`default`, `C`和`POSIX`排序規則。 PostgreSQL認為不同排序規則對象是不兼容的，即使他們有相同的屬性。例如： ``` SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1; ``` 將引起一個錯誤，即使`C`和`POSIX` 排序規則具有完全相同的行為。因此不推薦混合剝離的和非剝離的排序規則名。