Comment puis-je réaliser des recherches insensibles aux accents dans PostgreSQL ?

Approche PostgreSQL de la recherche insensible aux accents

Contrairement à certaines bases de données (comme Microsoft SQL Server), PostgreSQL ne prend pas en charge nativement les classements insensibles aux accents. Alors que PostgreSQL 12 a introduit des classements ICU non déterministes offrant une insensibilité à la casse et à l'accent, ceux-ci s'accompagnent de compromis en termes de performances et de restrictions opérationnelles.

Stratégies pour les requêtes insensibles aux accents dans PostgreSQL

Il existe plusieurs méthodes pour réaliser une recherche insensible aux accents dans PostgreSQL :

1. Le unaccentModule :

Ce module fournit la fonction unaccent(), supprimant les accents des chaînes. Cela permet des requêtes telles que :

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

Cependant, unaccent() n'est pas IMMUTABLE, ce qui empêche son utilisation dans les index d'expression, et il ne développe pas les ligatures (par exemple, 'Œ').

2. Wrapper de fonction C optimisé :

Pour répondre aux limitations de unaccent(), une solution plus efficace consiste à créer un wrapper de fonction C IMMUTABLE :

<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>

Cela permet la création d'index d'expression :

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

Les requêtes utilisent ensuite la fonction enveloppée :

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

3. Tirer parti de pg_trgm pour la correspondance de motifs et les ligatures :

Pour une correspondance de motifs et une gestion des ligatures plus flexibles, le module pg_trgm avec index trigrammes offre une solution puissante. Un index trigramme GIN permet des recherches insensibles à la casse et une détection de similarité :

<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>

Notez que pg_trgm les index sont plus gourmands en ressources que les index B-tree standard.

Le choix de l'approche optimale dépend des besoins spécifiques de votre application, de l'équilibre entre les performances des requêtes, les coûts de maintenance des index et les exigences de gestion des ligatures.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!