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 モジュールが強力なソリューションを提供します。 トライグラム 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 中国語 Web サイトの他の関連記事を参照してください。