Accueil › SQL & bases de données › Texte, recherche et collations
Texte, recherche et collations
Types et fonctionsintermediaire
Chercher du texte en SQL couvre trois besoins très différents, avec trois outils.
Préfixe — indexable directement :
CREATE INDEX ON produit (nom);
WHERE nom LIKE 'Clavier%' -- ✓ index utilisable
Sous-chaîne — il faut un index trigramme :
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX produit_nom_trgm ON produit USING gin (nom gin_trgm_ops);
WHERE nom ILIKE '%mécanique%' -- ✓ avec l'index trigramme
Recherche par mots — plein texte, avec lemmatisation :
CREATE INDEX produit_fts ON produit
USING gin (to_tsvector('french', nom || ' ' || COALESCE(description, '')));
WHERE to_tsvector('french', nom || ' ' || COALESCE(description, ''))
@@ plainto_tsquery('french', 'clavier mécanique');
Le plein texte trouve « claviers mécaniques » à partir de « clavier mécanique » : il normalise selon la langue, là où LIKE compare des caractères.
Pour la casse et les accents, la solution générale est l'index sur expression :
CREATE INDEX ON client (lower(nom));
WHERE lower(nom) = lower(:saisie); -- le prédicat doit être écrit à l'identique
Les termes à connaître
- LIKE / ILIKE
Recherche par motif :
%remplace n'importe quelle suite,_un caractère.ILIKEest la variante insensible à la casse, spécifique à PostgreSQL.- Index trigramme (pg_trgm)
Extension qui découpe le texte en séquences de trois caractères. Avec un index GIN ou GiST, elle rend efficace un
LIKE '%motif%'— sinon impossible à indexer.- tsvector / tsquery
Types de la recherche plein texte :
tsvectorest le document découpé en lexèmes normalisés,tsqueryla requête. L'opérateur@@teste la correspondance.- Lemmatisation (*stemming*)
Réduction d'un mot à sa racine selon la langue : « claviers », « clavier » donnent le même lexème. C'est ce qui distingue la recherche plein texte d'un simple
LIKE.- Collation
Les règles de comparaison et de tri du texte, dépendantes de la langue : en français, « é » se classe près de « e ». Elle est fixée à la création de la base et influence
ORDER BYcomme les index.- unaccent
Extension qui supprime les accents : « crème » devient « creme ». Combinée à
lower()dans un index sur expression, elle permet une recherche tolérante.
Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.