FTS5 (căutare full-text) în SQLite

CREATE VIRTUAL TABLE nume USING fts5(col1, col2, ... [, tokenize = '...']);
SELECT * FROM nume WHERE nume MATCH 'interogare' ORDER BY rank;

Ce face FTS5 (căutare full-text)

FTS5 este o extensie inclusă în SQLite care creează un tabel virtual cu un index de cuvinte. În loc de LIKE '%cuvant%', care citește tot textul, scrii WHERE articole MATCH 'cuvant' și SQLite găsește direct rândurile care conțin cuvântul.

Interogarea MATCH are limbajul ei: mai multe cuvinte înseamnă că toate trebuie să apară (AND implicit), OR și NOT combină condiții, "text exact" caută o frază, prog* caută după prefix, iar titlu: sqlite caută doar într-o coloană. Coloana ascunsă rank ordonează după relevanță (algoritmul BM25: rezultatele mai bune au valori mai mici).

Tokenizatorul implicit, unicode61, nu ține cont de literele mari și elimină diacriticele, așa că iasi găsește și „Iași”. Funcțiile highlight() și snippet() marchează cuvintele găsite. Tabelul FTS5 nu are tipuri, constrângeri sau indexuri proprii: ține textul pentru căutare, iar datele „adevărate” stau de obicei într-un tabel obișnuit.

Parametri

Parametru	Descriere
col1, col2	Coloanele de text indexate. Fără tipuri. UNINDEXED după nume păstrează coloana fără să o indexeze.
tokenize	Cum este împărțit textul în cuvinte: unicode61 (implicit), porter (rădăcini pentru engleză), trigram (subșiruri de 3 caractere, SQLite 3.34.0+).
MATCH 'interogare'	Cuvinte, AND/OR/NOT, fraze între ghilimele, prefix *, filtru coloana:, NEAR(a b, N).

Exemple

Exemplele de pe această pagină pornesc de la datele de mai jos. Rulează mai întâi acest cod, apoi exemplul.

CREATE VIRTUAL TABLE articole USING fts5(titlu, continut);
INSERT INTO articole (titlu, continut) VALUES
  ('Ghid SQLite', 'SQLite este o bază de date fără server, potrivită pentru aplicații mici.'),
  ('Vremea în Iași', 'Ploi slabe în Iași și Pașcani, temperaturi de 12 grade.'),
  ('Programare în C', 'Programarea în C cere atenție la pointeri și la memorie.'),
  ('Baze de date în Python', 'Modulul sqlite3 din Python lucrează direct cu fișiere SQLite.'),
  ('Rețete de toamnă', 'Plăcintă cu dovleac și mere, rețeta bunicii din Suceava.'),
  ('Excursie la Cluj', 'Trei zile în Cluj: muzee, Grădina Botanică și cafenele.');

Exemplul 1: Căutare simplă, ordonată după relevanță

„sqlite” apare în două din cele șase articole. „Ghid SQLite” are cuvântul și în titlu, și în conținut, deci are scorul cel mai bun (cel mai mic). În „sqlite3” cuvântul este altul, deci nu se potrivește.

SELECT titlu, round(rank, 2) AS scor
FROM articole
WHERE articole MATCH 'sqlite'
ORDER BY rank;

Rezultat

╭────────────────────────┬───────╮
│         titlu          │ scor  │
╞════════════════════════╪═══════╡
│ Ghid SQLite            │ -0.79 │
│ Baze de date în Python │ -0.57 │
╰────────────────────────┴───────╯

Exemplul 2: Fără diacritice, prefix și filtru pe coloană

iasi găsește „Iași”, pentru că tokenizatorul implicit elimină diacriticele. program* găsește articolul cu „Programare” în titlu și „Programarea” în conținut. titlu: date caută doar în titluri.

SELECT titlu FROM articole WHERE articole MATCH 'iasi';
SELECT titlu FROM articole WHERE articole MATCH 'program*';
SELECT titlu FROM articole WHERE articole MATCH 'titlu: date' ORDER BY titlu;

Rezultat

╭────────────────╮
│     titlu      │
╞════════════════╡
│ Vremea în Iași │
╰────────────────╯
╭─────────────────╮
│      titlu      │
╞═════════════════╡
│ Programare în C │
╰─────────────────╯
╭────────────────────────╮
│         titlu          │
╞════════════════════════╡
│ Baze de date în Python │
╰────────────────────────╯

Exemplul 3: Evidențierea rezultatelor

highlight(tabel, coloana, început, sfârșit) înconjoară cuvintele găsite; coloana se dă după poziție (1 = continut). snippet() întoarce doar un fragment de cel mult N cuvinte în jurul lor.

SELECT highlight(articole, 1, '[', ']') AS continut
FROM articole WHERE articole MATCH 'pointeri OR memorie';

SELECT snippet(articole, 1, '[', ']', '…', 5) AS fragment
FROM articole WHERE articole MATCH 'pascani';

Rezultat

╭──────────────────────────────────────────────────────────────╮
│                           continut                           │
╞══════════════════════════════════════════════════════════════╡
│ Programarea în C cere atenție la [pointeri] și la [memorie]. │
╰──────────────────────────────────────────────────────────────╯
╭─────────────────────────────────────╮
│              fragment               │
╞═════════════════════════════════════╡
│ …Iași și [Pașcani], temperaturi de… │
╰─────────────────────────────────────╯

Greșeli frecvente

• Folosești LIKE '%cuvant%' pe tabelul FTS5. Funcționează, dar citește tot tabelul și ignoră indexul de cuvinte; pentru căutare folosește MATCH.
• Pui în MATCH textul scris de utilizator așa cum este. Caractere ca -, " sau : au sens special și pot da fts5: syntax error; pune textul între ghilimele duble (dublând ghilimelele din el) sau curăță-l înainte.
• Ordonezi crescător după bm25() și crezi că primele rezultate sunt cele mai slabe. În FTS5, scorul mai mic (mai negativ) înseamnă relevanță mai mare, de aceea ORDER BY rank pune cele mai bune rezultate primele.

Comenzi înrudite

• CREATE TABLE: Creează un tabel nou. În SQLite tipul coloanei este opțional și doar sugerează cum se salvează valorile.
• Index pe expresii: Indexează rezultatul unei expresii, de exemplu lower(email) sau un câmp JSON, ca să cauți rapid după valoarea calculată.
• SQLite din Python (sqlite3): Modulul sqlite3 din biblioteca standard Python: conectare, interogări cu parametri, tranzacții și rânduri ca dicționare.