Wie kann ich eine akzentunabhängige Suche in PostgreSQL erreichen?

PostgreSQLs Ansatz zur akzentunabhängigen Suche

Im Gegensatz zu einigen Datenbanken (wie Microsoft SQL Server) unterstützt PostgreSQL nativ keine akzentunabhängigen Sortierungen. Mit PostgreSQL 12 wurden zwar nicht deterministische ICU-Sortierungen eingeführt, die Groß- und Kleinschreibung und Akzente berücksichtigen, diese sind jedoch mit Leistungseinbußen und Betriebseinschränkungen verbunden.

Strategien für akzentunabhängige Abfragen in PostgreSQL

Es gibt mehrere Methoden, um eine akzentunabhängige Suche in PostgreSQL zu erreichen:

1. Das unaccentModul:

Dieses Modul bietet die Funktion unaccent(), mit der Akzente aus Zeichenfolgen entfernt werden. Dies ermöglicht Abfragen wie:

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

Allerdings ist unaccent() nicht unveränderlich, was seine Verwendung in Ausdrucksindizes verhindert, und es erweitert keine Ligaturen (z. B. 'Œ').

2. Optimierter C-Funktions-Wrapper:

Um die Einschränkungen von unaccent() zu beseitigen, besteht eine effizientere Lösung darin, einen IMMUTABLE C-Funktionswrapper zu erstellen:

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

Dies ermöglicht die Erstellung von Ausdrucksindizes:

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

Abfragen verwenden dann die Wrapped-Funktion:

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

3. Nutzung von pg_trgm für Mustervergleich und Ligaturen:

Für eine flexiblere Mustererkennung und Ligaturverarbeitung bietet das Modul pg_trgm mit Trigramm-Indizes eine leistungsstarke Lösung. Ein Trigramm-GIN-Index ermöglicht Suchen ohne Berücksichtigung der Groß- und Kleinschreibung und Ähnlichkeitserkennung:

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

Beachten Sie, dass pg_trgm Indizes ressourcenintensiver sind als Standard-B-Tree-Indizes.

Die Wahl des optimalen Ansatzes hängt von den spezifischen Anforderungen Ihrer Anwendung ab, wobei die Abfrageleistung mit den Indexwartungskosten und den Anforderungen für die Ligaturverarbeitung in Einklang gebracht werden muss.

Das obige ist der detaillierte Inhalt vonWie kann ich eine akzentunabhängige Suche in PostgreSQL erreichen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!