如何在 PostgreSQL 中實現不區分重音的搜尋？

PostgreSQL 的不區分重音的搜尋方法

與某些資料庫（如 Microsoft SQL Server）不同，PostgreSQL 本身並不支援不區分重音的排序規則。 雖然 PostgreSQL 12 引入了非確定性 ICU 排序規則，不區分大小寫和重音，但這些排序規則會帶來效能權衡和操作限制。

PostgreSQL 中不區分重音的查詢策略

有幾種方法可以在 PostgreSQL 中實現不區分重音的搜尋：

1。 unaccent 模組：

此模組提供unaccent()函數，從字串中刪除重音符號。 這允許查詢如下：

<code class="language-sql">SELECT * FROM users WHERE unaccent(name) = unaccent('João');</code>

但是，unaccent() 不是 IMMUTABLE，這會阻止其在表達式索引中使用，而且它不會擴展連字（例如「Œ」）。

2。最佳化的 C 函數包裝器：

為了解決 unaccent() 的限制，更有效的解決方案涉及建立 IMMUTABLE C 函數包裝器：

<code class="language-sql">CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
RETURN public.immutable_unaccent(regdictionary 'public.unaccent', );</code>

這允許建立表達式索引：

<code class="language-sql">CREATE INDEX users_unaccent_name_idx ON users(public.f_unaccent(name));</code>

然後使用包裝函數進行查詢：

<code class="language-sql">SELECT * FROM users WHERE f_unaccent(name) = f_unaccent('João');</code>

3。利用 pg_trgm 進行模式匹配與連字：

為了更靈活的模式匹配和連字處理，具有三元組索引的 pg_trgm 模組提供了強大的解決方案。 trigram GIN 索引支援不區分大小寫的搜尋和相似性偵測：

<code class="language-sql">CREATE INDEX users_unaccent_name_trgm_idx ON users
USING gin (f_unaccent(name) gin_trgm_ops);

SELECT * FROM users WHERE f_unaccent(name) LIKE ('%' || f_unaccent('João') || '%');</code>

請注意，pg_trgm 索引比標準 B 樹索引更耗費資源。

選擇最佳方法取決於應用程式的特定需求，平衡查詢效能與索引維護成本以及連字處理的要求。

以上是如何在 PostgreSQL 中實現不區分重音的搜尋？的詳細內容。更多資訊請關注PHP中文網其他相關文章！