F.7. citext · PostgreSQL 中文文檔 9.3

# F.7\. citext `citext`模塊提供不區分大小寫字符串類型，`citext`。從本質上講，當比較值時，它內部調用`lower`。否則，它的操作很像`text`。 ## F.7.1\. 基本原理當比較值時，在PostgreSQL中執行不區分大小寫匹配的標準方法曾使用 `lower`函數，比如 ``` SELECT * FROM tab WHERE lower(col) = LOWER(?); ``` 這個執行的相當好，但有一些缺點： * 它使你的SQL語句冗長，并且你總是在列和查詢值上使用`lower`。 * 它不使用索引，除非你使用`lower`創建一個函數索引。 * 如果你聲明列為 `UNIQUE`或者`PRIMARY KEY`，隱式產生的索引是大小寫敏感的。因此對不區分大小寫搜索無用，并且它不會不區分大小寫。 `citext`數據類型允許你在SQL查詢中刪除調用`lower`，并且允許主鍵不區分大小寫。`citext`是區域意識，就像`text`，這意味著大寫字母和小寫字母字符的匹配依賴于數據庫的`LC_CTYPE`設置規則。另外，這種操作與查詢中`lower`的使用是相同的。但是因為它通過數據類型透明地完成，你無須記得在你的查詢中執行任何特別的。 ## F.7.2\. 如何使用它這是用法的一個簡單例子： ``` CREATE TABLE users ( nick CITEXT PRIMARY KEY, pass TEXT NOT NULL ); INSERT INTO users VALUES ( 'larry', md5(random()::text) ); INSERT INTO users VALUES ( 'Tom', md5(random()::text) ); INSERT INTO users VALUES ( 'Damian', md5(random()::text) ); INSERT INTO users VALUES ( 'NEAL', md5(random()::text) ); INSERT INTO users VALUES ( 'Bj?rn', md5(random()::text) ); SELECT * FROM users WHERE nick = 'Larry'; ``` `SELECT`語句將返回一個元組，盡管`nick`列被設置為`larry`，并且查詢為`Larry`。 ## F.7.3\. 字符串比較操作 `citext`通過轉換每個字符串到小寫執行比較（盡管調用`lower`）并且然后通常比較結果。因此，比如，考慮兩個字符串相等，如果 `lower`為了它們可能產生相同結果。為了盡可能地模擬不區分大小寫排序規則，有一些字符串處理操作符和函數的`citext`特定版本。所以，比如，當應用于`citext`:他們不區分大小寫匹配，正則表達式運算符`~`和`~*`表現出相同操作。對于`!~` and `!~*`以及 `LIKE`運算符`~~`和`~~*`和 `!~~`和`!~~*`同樣是真，如果你想要匹配大小寫敏感，你可以投射運算符的參數給`text`。類似地，如果它們參數是`citext`，所有下面的函數執行不區分大小寫匹配： * `regexp_replace()` * `regexp_split_to_array()` * `regexp_split_to_table()` * `replace()` * `split_part()` * `strpos()` * `translate()` 對于正則表達式函數，如果你想要匹配大小寫敏感，你可以聲明"c"標記以強迫大小寫匹配。否則，如果你想要大小寫敏感操作，你必須在使用這些函數之一前投射到`text`。 ## F.7.4\. 限制 * `citext`的折疊操作依賴于你的數據庫的`LC_CTYPE`設置。當創建數據庫時，決定如何比較值。通過Unicode標準定義的術語中不是真的大小寫不敏感。實際上，這意味著，只要你對你的排序規則滿意，你應該對`citext`的比較感到滿意。但是如果你有數據以不同語言存儲在數據庫中，如果排序規則為另外一種語言，那么一種語言的用戶可能發現查詢結果不如預期。 * 作為PostgreSQL 9.1，你可以附屬`COLLATE`規范到`citext` 列或者數據值。當比較折疊字符串時， `citext`運算符將接受非缺省`COLLATE`規范，但小寫的起初折疊總是按照數據庫的`LC_CTYPE`設置被執行（即，即使給定`COLLATE "default"` ）。這可能在未來版本中被改變，因此這兩個步驟遵循輸入`COLLATE` 規范。 * `citext`不像`text`一樣有效，因為運算符函數和B樹比較函數必須開始數據拷貝，并且為了比較將它轉換為小寫。然而，它比起使用`lower`獲取大小寫不敏感匹配更加有效。 * 如果你需要數據比較某些情況中大小寫敏感和其他情況中大小寫不敏感， `citext`沒有太大幫助。當你需要不區分大小寫比較時，標準答案是使用`text`類型并且手動使用`lower`函數。如果很少需要不區分大小寫比較，那么它執行正確。如果你需要不區分大小寫行為大多數時間并且很少不區分大小寫，當你想要區分大小寫比較時，考慮存儲數據為`citext` 并且明確投射該列到`text`。如果你想要快速搜索的兩個類型，在這兩種情況下，你將需要兩個索引。 * 包含`citext`運算符的模式必須在當前的`search_path` (通常`public`)中; 如果不是，相反調用正常的大小寫敏感`text`運算符。 ## F.7.5\. 作者 David E. Wheeler `<[david@kineticode.com](mailto:david@kineticode.com)>` 靈感來源于Donald Fraser的最初的`citext`模塊。