MATCH() … AGAINST() în MySQL

MATCH(coloana1, coloana2, ...) AGAINST (text_cautat [mod])

mod: IN NATURAL LANGUAGE MODE (implicit)
     IN BOOLEAN MODE
     WITH QUERY EXPANSION

Ce face MATCH() ... AGAINST()

MATCH() ... AGAINST() caută cuvinte în texte lungi mult mai rapid decât LIKE '%cuvânt%', folosind un index FULLTEXT. Coloanele din MATCH() trebuie să fie exact cele din index (aceeași listă). Folosită în WHERE, returnează rândurile ordonate după relevanță; folosită în SELECT, dă scorul (0 = fără potrivire).

Modul implicit (limbaj natural) caută oricare dintre cuvinte și calculează relevanța: cuvintele rare contează mai mult. În IN BOOLEAN MODE folosești operatori: + (cuvânt obligatoriu), - (cuvânt interzis), * la final (prefix) și ghilimele pentru o expresie exactă. WITH QUERY EXPANSION caută de două ori, a doua oară adăugând cuvinte din cele mai relevante rezultate.

La InnoDB, cuvintele mai scurte de 3 caractere (innodb_ft_min_token_size) și cuvintele din lista de cuvinte ignorate (în engleză, de exemplu the) nu sunt indexate. Comparația urmează colația, deci implicit nu contează diacriticele și majusculele.

Parametri

Parametru	Descriere
coloana1, coloana2, ...	Coloanele unui index FULLTEXT, în aceeași listă.
text_cautat	Cuvintele căutate (constantă text).
mod	Opțional. Modul de căutare.

Ce returnează

Un număr real: scorul de relevanță (0 dacă nu se potrivește).

Exemple

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

CREATE TABLE articole (
  id INT AUTO_INCREMENT PRIMARY KEY,
  titlu VARCHAR(100),
  continut TEXT,
  FULLTEXT (titlu, continut)
) ENGINE=InnoDB;

INSERT INTO articole (titlu, continut) VALUES
  ('Rețete de post', 'Ciorbă de legume și sarmale cu orez, fără carne.'),
  ('Ghid MySQL pentru începători', 'Învață să creezi tabele și să scrii interogări SELECT în MySQL.'),
  ('Excursie la Iași', 'Palatul Culturii, Copou și cele mai bune restaurante din Iași.'),
  ('Optimizarea interogărilor MySQL', 'Indexurile fac interogările mult mai rapide pe tabele mari.'),
  ('Sarmale ca la bunica', 'Rețeta tradițională de sarmale în foi de varză murată.'),
  ('Backup pentru baze de date', 'Cum faci copii de siguranță cu mysqldump în fiecare noapte.');

Exemplul 1: Căutare simplă, cu scor

SELECT id, titlu, ROUND(MATCH(titlu, continut) AGAINST ('sarmale'), 4) AS relevanta
FROM articole
WHERE MATCH(titlu, continut) AGAINST ('sarmale');

Rezultat

+----+----------------------+-----------+
| id | titlu                | relevanta |
+----+----------------------+-----------+
|  5 | Sarmale ca la bunica |    0.4553 |
|  1 | Rețete de post       |    0.2276 |
+----+----------------------+-----------+

Exemplul 2: Modul boolean: obligatoriu, interzis, prefix, expresie

SELECT id, titlu FROM articole
WHERE MATCH(titlu, continut) AGAINST ('+mysql -backup' IN BOOLEAN MODE);

SELECT id, titlu FROM articole
WHERE MATCH(titlu, continut) AGAINST ('interog*' IN BOOLEAN MODE);

SELECT id, titlu FROM articole
WHERE MATCH(titlu, continut) AGAINST ('"foi de varză"' IN BOOLEAN MODE);

Rezultat

+----+----------------------------------+
| id | titlu                            |
+----+----------------------------------+
|  2 | Ghid MySQL pentru începători     |
|  4 | Optimizarea interogărilor MySQL  |
+----+----------------------------------+
+----+----------------------------------+
| id | titlu                            |
+----+----------------------------------+
|  2 | Ghid MySQL pentru începători     |
|  4 | Optimizarea interogărilor MySQL  |
+----+----------------------------------+
+----+----------------------+
| id | titlu                |
+----+----------------------+
|  5 | Sarmale ca la bunica |
+----+----------------------+

Exemplul 3: Fără diacritice și cu extinderea căutării

„Iasi” găsește „Iași” datorită colației. Cu WITH QUERY EXPANSION, a doua căutare adaugă cuvinte din primele rezultate, așa că apar și articole fără cuvântul „tabele”.

SELECT id, titlu FROM articole
WHERE MATCH(titlu, continut) AGAINST ('Iasi');

SELECT id, titlu FROM articole
WHERE MATCH(titlu, continut) AGAINST ('tabele' WITH QUERY EXPANSION);

Rezultat

+----+-------------------+
| id | titlu             |
+----+-------------------+
|  3 | Excursie la Iași  |
+----+-------------------+
+----+----------------------------------+
| id | titlu                            |
+----+----------------------------------+
|  4 | Optimizarea interogărilor MySQL  |
|  2 | Ghid MySQL pentru începători     |
|  3 | Excursie la Iași                 |
|  6 | Backup pentru baze de date       |
+----+----------------------------------+

Greșeli frecvente

• Scrii MATCH(titlu) când indexul este pe (titlu, continut). Primești eroarea Can't find FULLTEXT index matching the column list; lista coloanelor trebuie să fie identică cu a indexului (sau creezi alt index).
• Cauți cuvinte de 1-2 litere sau cuvinte uzuale în engleză. Nu sunt indexate, deci nu găsești nimic.
• Folosești WITH QUERY EXPANSION pentru căutări obișnuite. Aduce și rezultate fără legătură; e util doar pentru căutări foarte scurte.

Comenzi înrudite

• LIKE: Caută texte după un model: începe cu, se termină cu, conține.
• REGEXP / RLIKE: Verifică dacă un text se potrivește cu o expresie regulată; mai puternic decât LIKE.
• CREATE INDEX: Creează un index, care face căutările după o coloană mult mai rapide.