SQL Busca de Texto (FTS5)

O site da Loja Aurora vai ganhar uma caixa de busca. O cliente digita "lapis", sem acento, e espera achar o "Lápis Grafite HB". Digita "escolar" e espera ver primeiro a Mochila Escolar, e não um caderno qualquer. Com o LIKE (aula LIKE) isso não dá certo. Para isso existe a busca de texto completo, que no SQLite se chama FTS5. Nesta aula você monta a busca da loja: palavras, frases, começo de palavra, ordem por relevância, a palavra encontrada em destaque e o problema dos acentos.

Você vai aprender

O que é uma busca de texto completo e por que ela é diferente do LIKE.
Criar uma tabela de busca com CREATE VIRTUAL TABLE … USING fts5 e enchê-la com os produtos.
Buscar com MATCH: uma palavra, várias, OR, NOT, frase entre aspas, começo de palavra (cad*), uma coluna só.
Ordenar pelos melhores resultados com rank e bm25().
Destacar a palavra encontrada com highlight() e mostrar um trecho com snippet().
Acentos e maiúsculas: o tokenize e o remove_diacritics 2.
As mensagens reais de erro do FTS5, e o que acontece quando o produto muda.

Todos os exemplos rodam de verdade no seu navegador, no SQLite 3.50 com o banco da Loja Aurora. O FTS5 é uma extensão que vem junto no SQLite deste guia, no editor e no terminal da Ana.

A ideia: o índice remissivo do livro

No fim de um livro de receitas há um índice remissivo: uma lista de palavras em ordem alfabética, e ao lado de cada uma as páginas em que ela aparece. "Cenoura: 12, 47, 80." Para achar todas as receitas com cenoura, você não lê o livro inteiro: vai direto na palavra.

O LIKE '%cenoura%' faz o contrário: lê o livro inteiro, página por página, procurando aquelas letras. Funciona, mas tem três problemas:

O cliente quer…	Com `LIKE`	Com FTS5
Achar "Lápis" digitando `lapis`	Não acha: `á` e `a` são letras diferentes	Acha: o índice guarda as palavras sem acento
Ver primeiro o resultado mais certeiro	Não sabe: só diz "tem" ou "não tem"	Dá uma nota de relevância para cada resultado
Buscar rápido numa loja com milhares de produtos	Lê todas as linhas, sempre	Vai direto na palavra, como o índice do livro

O FTS5 (de Full-Text Search, "busca de texto completo", versão 5) monta esse índice remissivo. Ele quebra cada texto em palavras (os tokens), deixa todas em minúsculas e sem acento, e anota em que linha cada palavra aparece. Quem faz a quebra é o tokenizador; você vai voltar a ele na seção 6.

1. Criando a tabela de busca

A busca fica numa tabela especial, uma tabela virtual: por fora ela se parece com uma tabela comum (tem colunas, aceita INSERT e SELECT), mas por dentro quem cuida dos dados é o FTS5, com o índice de palavras. A Ana cria a tabela busca com duas colunas de texto, o nome do produto e o texto da categoria, e copia os 45 produtos para lá:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);

INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao
FROM produtos p
JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome, categoria
FROM busca
WHERE busca MATCH 'azul';

CREATE VIRTUAL TABLE busca: "Crie a tabela virtual busca". VIRTUAL quer dizer que uma extensão vai cuidar dela.
USING fts5: "Usando o FTS5": a extensão que cuida da tabela.
(nome, categoria): As colunas, só com o nome. No FTS5 não se escreve tipo (TEXT, INTEGER…), nem NOT NULL, nem chave: toda coluna é texto para buscar.
INSERT INTO busca (rowid, nome, categoria) SELECT …: Copia os produtos (aula INSERT INTO SELECT). O rowid é o número de cada linha da tabela; a Ana guarda nele o id do produto, para depois voltar à tabela produtos.
c.nome || ': ' || c.descricao: O texto da categoria, como "Papelaria: Material escolar e de escritório". O || junta textos.
WHERE busca MATCH 'azul': A busca. MATCH é "combina com". À esquerda vai o nome da tabela (quer dizer "em qualquer coluna"); à direita, o que procurar.

Achou os dois produtos com a palavra "azul", e o rowid de cada um é o id do produto na tabela produtos. Repare que a busca foi por palavra inteira, e que maiúsculas não importam: 'azul' achou Azul.

Por que cada exemplo cria a tabela de novo?

A tabela busca não existe no banco da Aurora: ela é criada no exemplo, e cada Rodar do editor parte do banco limpo (aula O Editor do Guia). Por isso, todos os exemplos desta aula começam com as mesmas duas linhas: o CREATE VIRTUAL TABLE e o INSERT. No banco de verdade, você cria uma vez só.

Voltando aos produtos

A tabela de busca serve para achar; o preço, o estoque e o resto continuam em produtos. Um JOIN pelo rowid traz tudo:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT p.id, p.nome, p.preco, p.estoque
FROM busca b
JOIN produtos p ON p.id = b.rowid
WHERE busca MATCH 'garrafa';

Repare que, mesmo com o apelido b, o MATCH continua usando o nome da tabela: busca MATCH ….

2. Uma palavra, várias palavras

Com duas palavras separadas por espaço, o FTS5 procura linhas que tenham as duas, em qualquer ordem e em qualquer coluna:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome, categoria
FROM busca
WHERE busca MATCH 'escolar azul';

A Mochila tem "Escolar" e "Azul" no nome. A Caneta tem "Azul" no nome e "escolar" na categoria ("Material escolar e de escritório"). Para o FTS5, as duas linhas têm as duas palavras. Se você quer procurar só no nome, veja a seção 3.

Os operadores: `AND`, `OR`, `NOT`

Dentro do texto da busca, três palavras em MAIÚSCULAS são operadores, como no WHERE (aulas AND, OR e NOT):

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'garrafa OR copos';
SELECT rowid, nome FROM busca WHERE busca MATCH 'jogo NOT panelas';
SELECT rowid, nome FROM busca WHERE busca MATCH '(garrafa OR copos) NOT squeeze';

Busca	Acha as linhas que têm…
`'escolar azul'` ou `'escolar AND azul'`	as duas palavras
`'garrafa OR copos'`	pelo menos uma das duas
`'jogo NOT panelas'`	"jogo" e não "panelas"
`'(garrafa OR copos) NOT squeeze'`	os parênteses agrupam, como numa conta

Operador em minúsculas vira palavra

'jogo and panelas', com and minúsculo, procura três palavras: "jogo", "and" e "panelas". Como nenhum produto tem a palavra "and", a busca volta vazia, sem erro nenhum. No FTS5, os operadores só valem em MAIÚSCULAS.

Uma frase exata: aspas duplas

Entre aspas duplas, as palavras têm de aparecer juntas e nessa ordem. Como a busca inteira já é um texto do SQL, entre aspas simples, a frase fica assim: '"de cozinha"'.

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'cozinha';
SELECT rowid, nome FROM busca WHERE busca MATCH '"de cozinha"';

A primeira busca achou 6 produtos: todos os da categoria "Casa e Cozinha" têm a palavra "cozinha" na coluna categoria. A frase "de cozinha" só aparece, assim, juntinha, no "Avental de Cozinha".

Palavras perto uma da outra: `NEAR`

NEAR(jogo peças, 3) acha as linhas em que "jogo" e "peças" aparecem com no máximo 3 palavras entre elas. Sem o número, o limite é 10.

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'NEAR(jogo peças, 3)';
SELECT rowid, nome FROM busca WHERE busca MATCH 'NEAR(jogo peças, 2)';

Em "Jogo de Panelas 5 peças", entre "Jogo" e "peças" há três palavras: "de", "Panelas" e "5". Com limite 3, achou; com limite 2, não.

3. Começo de palavra e uma coluna só

O prefixo: `cad*`

O FTS5 procura palavras inteiras. Se o cliente digitou só "car", não acha nada, porque nenhum produto tem a palavra "car". Com um asterisco no fim, car* quer dizer "qualquer palavra que começa com car":

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'cad';
SELECT rowid, nome FROM busca WHERE busca MATCH 'cad*';

A busca 'cad' volta vazia; a 'cad*' acha o "Caderno". É o que uma caixa de busca faz enquanto o cliente ainda está digitando. O asterisco só funciona no fim: o FTS5 não procura "palavras que terminam com" (veja Quando dá errado).

Missão: o cliente digitou só "car" na busca da loja e não apareceu nada. Faça a busca achar os produtos com alguma palavra que começa com "car" (são dois).

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome
FROM busca
WHERE busca MATCH 'car';

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome
FROM busca
WHERE busca MATCH 'car*';

Buscar numa coluna só

Para procurar só no nome do produto, há duas escritas: o nome da coluna à esquerda do MATCH, ou o filtro nome: dentro da busca:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'escolar';
SELECT rowid, nome FROM busca WHERE nome  MATCH 'escolar';
SELECT rowid, nome FROM busca WHERE busca MATCH 'nome: escolar';

Em todas as colunas, "escolar" acha 7 produtos: os 6 da Papelaria (pela categoria) e o Dicionário Escolar (pelo nome). Só no nome, acha 2. As duas escritas da coluna dão o mesmo resultado; o filtro nome: tem a vantagem de caber dentro de uma busca maior, como 'nome: escolar OR categoria: livros'.

4. Os melhores primeiro: `rank` e `bm25()`

O cliente que digita "escolar" quer ver primeiro a Mochila Escolar e o Dicionário Escolar, não o Lápis (que só tem "escolar" na categoria). O FTS5 calcula, para cada resultado, uma nota de relevância, e você ordena por ela com ORDER BY rank:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome, round(rank, 3) AS nota
FROM busca
WHERE busca MATCH 'escolar'
ORDER BY rank;

rank: Uma coluna escondida que toda tabela FTS5 tem. Ela só tem valor numa busca com MATCH: é a nota daquela linha para aquela busca.
ORDER BY rank: Do melhor para o pior. A nota é negativa e, quanto menor, melhor: −2.307 vem antes de −1.628. Por isso é ORDER BY rank, sem DESC.
round(rank, 3): Só para mostrar a nota com três casas. Os números exatos não importam; importa a ordem.

A nota vem de uma fórmula chamada BM25, a mesma família usada por muitos buscadores. Em palavras simples, ela dá mais pontos quando:

a palavra é rara na tabela inteira (achar "jardinagem" vale mais que achar "de");
a palavra aparece mais vezes na linha;
a linha é curta (a palavra ocupa uma parte maior do texto).

A Mochila ganhou porque tem "escolar" duas vezes (no nome e na categoria). O Dicionário veio em segundo: só tem "escolar" no nome, mas o texto dele é curto.

Pesos por coluna: `bm25()`

O rank é o mesmo que a função bm25(busca). Chamando a função direto, você pode dar um peso para cada coluna, na ordem em que elas foram criadas. A Ana acha que uma palavra no nome vale 10 vezes mais que na categoria:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome, round(bm25(busca, 10.0, 1.0), 3) AS nota
FROM busca
WHERE busca MATCH 'escolar'
ORDER BY nota;

Com peso 10 no nome, o Dicionário Escolar passou na frente da Mochila: os dois têm "escolar" no nome, e o nome do Dicionário é mais curto. Os de baixo, que só têm a palavra na categoria, continuaram com as mesmas notas.

5. Destacando o que achou: `highlight()` e `snippet()`

Num buscador, a palavra encontrada aparece em negrito. O highlight() ("destaque") devolve o texto de uma coluna com marcas em volta de cada palavra encontrada:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid,
       highlight(busca, 0, '[', ']')          AS produto,
       snippet(busca, 1, '[', ']', '…', 4)    AS onde
FROM busca
WHERE busca MATCH 'escolar'
ORDER BY rank;

highlight(busca, 0, '[', ']'): Da tabela busca, a coluna 0 (a primeira, nome; a contagem começa do zero), com [ antes e ] depois de cada palavra encontrada. Num site, em vez de colchetes, o programa usaria as tags do negrito.
snippet(busca, 1, '[', ']', '…', 4): "Trecho": um pedaço curto da coluna 1 (categoria) em volta da palavra encontrada. Os argumentos são: a coluna, a marca de antes, a de depois, o que pôr onde o texto foi cortado (…) e quantas palavras mostrar, no máximo (4). Use -1 na coluna para o FTS5 escolher a melhor.

Repare na linha do Dicionário: a categoria dele ("Livros: Livros, dicionários e guias") não tem "escolar", então o snippet() mostrou só o começo, sem destaque. E a palavra destacada mantém a escrita original, com maiúscula e acento: o destaque é sobre o texto guardado, não sobre a busca.

Experimente: troque a busca por outra ('jardim OR jardinagem', 'livro*', '"de pano"'), mude as marcas do destaque para '**' e '**', ou dê pesos com bm25(busca, 5.0, 1.0).

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, highlight(busca, 0, '[', ']') AS produto, round(rank, 3) AS nota
FROM busca
WHERE busca MATCH 'azul OR escolar'
ORDER BY rank;

6. Acentos e maiúsculas: o tokenizador

Lembra do tokenizador, que quebra o texto em palavras? O FTS5 usa, se você não disser nada, o tokenizador unicode61. Ele faz três coisas com cada palavra antes de guardar no índice: separa nos espaços e pontuação, põe em minúsculas e tira os acentos. A busca passa pelo mesmo tratamento. Por isso "lapis" acha "Lápis":

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'lapis';
SELECT rowid, nome FROM busca WHERE busca MATCH 'LÁPIS';
SELECT nome FROM produtos WHERE nome LIKE '%lapis%';

O FTS5 achou com lapis e com LÁPIS. O LIKE não achou nada: para ele, á e a são letras diferentes.

A opção `remove_diacritics`

Quem decide se os acentos (os diacríticos) saem é a opção remove_diacritics do tokenizador, escrita no tokenize = '…' do CREATE:

Valor	O que faz
`0`	Mantém os acentos: `lapis` e `lápis` viram palavras diferentes.
`1` (o padrão)	Tira os acentos das letras latinas, mas deixa passar letras com dois acentos, como o `ệ` do vietnamita.
`2`	Tira os acentos de todas as letras latinas, inclusive as de dois acentos. É o mais completo.

Veja os três lado a lado. Cada tabela recebe os 45 nomes de produtos e mais um, "Phở Việt" (um prato vietnamita), para mostrar a diferença entre o 1 e o 2:

CREATE VIRTUAL TABLE b0 USING fts5(nome, tokenize = 'unicode61 remove_diacritics 0');
CREATE VIRTUAL TABLE b1 USING fts5(nome);
CREATE VIRTUAL TABLE b2 USING fts5(nome, tokenize = 'unicode61 remove_diacritics 2');
INSERT INTO b0 SELECT nome FROM produtos UNION ALL SELECT 'Phở Việt';
INSERT INTO b1 SELECT nome FROM produtos UNION ALL SELECT 'Phở Việt';
INSERT INTO b2 SELECT nome FROM produtos UNION ALL SELECT 'Phở Việt';

SELECT 'com 0' AS tabela, nome FROM b0 WHERE b0 MATCH 'lapis'
UNION ALL SELECT 'com 1', nome FROM b1 WHERE b1 MATCH 'lapis'
UNION ALL SELECT 'com 2', nome FROM b2 WHERE b2 MATCH 'lapis'
UNION ALL SELECT 'com 1', nome FROM b1 WHERE b1 MATCH 'viet'
UNION ALL SELECT 'com 2', nome FROM b2 WHERE b2 MATCH 'viet';

Com 0, "lapis" não acha "Lápis": o acento ficou no índice.
Com 1 e com 2, acha.
Só com 2, "viet" acha "Việt": o ệ tem dois sinais (o circunflexo e o ponto embaixo), e o 1 não dá conta dele.

Para o português, que nunca tem dois acentos na mesma letra, o padrão já resolve. Mesmo assim, a recomendação é escrever tokenize = 'unicode61 remove_diacritics 2' ao criar uma busca nova: fica claro para quem lê o CREATE que os acentos saem, e nomes estrangeiros (fornecedores, autores de livros) também ficam cobertos.

Só a busca ignora os acentos

O que fica sem acento é o índice. O texto guardado na tabela continua igual, com acento. Por isso o SELECT nome mostra "Lápis Grafite HB" e o highlight() destaca a palavra como ela foi escrita.

Pedaços de palavra: o tokenizador `trigram`

E se o cliente digitar um pedaço do meio da palavra, como "rinh"? O unicode61 só conhece palavras inteiras (ou começos, com *). O tokenizador trigram guarda cada grupo de 3 letras seguidas e acha pedaços em qualquer lugar da palavra:

CREATE VIRTUAL TABLE tri USING fts5(nome, tokenize = 'trigram');
INSERT INTO tri (rowid, nome) SELECT id, nome FROM produtos;

SELECT rowid, nome FROM tri WHERE tri MATCH 'rinh';

O preço: o índice fica bem maior, a busca precisa de pelo menos 3 letras, e não há a ideia de "palavra" (nem relevância por palavra). Para a caixa de busca de uma loja, o unicode61 com * costuma bastar.

7. A tabela de busca é uma cópia

A tabela busca recebeu uma cópia dos nomes. Se o produto mudar depois, a cópia não fica sabendo. A caneta azul virou preta:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

UPDATE produtos SET nome = 'Caneta Esferográfica Preta' WHERE id = 2;

SELECT rowid, nome FROM busca WHERE busca MATCH 'azul';
SELECT rowid, nome FROM busca WHERE busca MATCH 'preta';

A busca por "azul" ainda acha a caneta, e "preta" não acha nada. Quem muda o produto precisa mudar a cópia também. O jeito mais simples é refazê-la de tempos em tempos (apagar tudo e copiar de novo); o jeito profissional é um gatilho (trigger), um comando que o banco roda sozinho a cada INSERT, UPDATE ou DELETE em produtos. Refazendo:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;
UPDATE produtos SET nome = 'Caneta Esferográfica Preta' WHERE id = 2;

DELETE FROM busca;
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'preta';

Formas diferentes de fazer a mesma coisa

Três jeitos de achar a Garrafa Squeeze, que tem "squeeze" no nome:

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'squeeze';
SELECT rowid, nome FROM busca WHERE busca = 'squeeze';
SELECT rowid, nome FROM busca('squeeze');

As três são a mesma busca: o = com o nome da tabela e a tabela chamada como função, busca('…'), são atalhos do FTS5 para o MATCH. Prefira o MATCH, que é o que todo mundo reconhece.

Para…	Use	Por quê
Uma caixa de busca de produtos, artigos, mensagens	FTS5 com `unicode61 remove_diacritics 2`	Ignora acento e maiúscula, ordena por relevância, é rápido
Achar enquanto o cliente digita	FTS5 com prefixo: `'cad*'`	Acha pelo começo da palavra
Achar um pedaço do meio da palavra	FTS5 com `trigram`	Acha qualquer pedaço de 3 letras ou mais
Um filtro simples numa tabela pequena (`nome LIKE 'Ana %'`)	`LIKE`	Não precisa de tabela extra nem de cópia
Um código exato (`'USB-C'`, um CPF)	`=` ou `LIKE` na tabela comum	O tokenizador quebraria o código em pedaços

Quando dá errado

Hífen na busca

O cliente digitou o nome do produto do jeito que está escrito, "marca-texto":

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'marca-texto';

A mensagem: no such column: texto, "não existe a coluna texto". Na linguagem de busca do FTS5, o hífen não faz parte da palavra; o FTS5 tentou ler o -texto como um filtro de coluna, e a tabela não tem coluna texto. O mesmo acontece com 'usb-c' (no such column: c).

Como consertar: ponha o termo entre aspas duplas, como frase: MATCH '"marca-texto"'. Dentro das aspas, o tokenizador quebra no hífen e procura "marca" seguida de "texto". Num site, o programa deve sempre pôr o que o cliente digitou entre aspas duplas (dobrando alguma aspa dupla que venha dentro) antes de mandar para o MATCH.

MATCH numa tabela comum

SELECT nome FROM produtos WHERE nome MATCH 'azul';

A mensagem: unable to use function MATCH in the requested context, "não foi possível usar a função MATCH neste contexto". O MATCH só funciona numa tabela FTS5. A tabela produtos é comum.

Como consertar: faça a busca na tabela de busca (busca MATCH 'azul') e volte aos produtos com um JOIN pelo rowid, como na seção 1.

Busca que termina num operador

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH 'azul AND';

A mensagem: fts5: syntax error near "", "erro de sintaxe do FTS5 perto de (nada)". Depois do AND deveria vir outra palavra, e veio o fim do texto: por isso o "perto de" está vazio.

Como consertar: complete a busca ('azul AND escolar') ou tire o operador.

Asterisco no começo

CREATE VIRTUAL TABLE busca USING fts5(nome, categoria);
INSERT INTO busca (rowid, nome, categoria)
SELECT p.id, p.nome, c.nome || ': ' || c.descricao FROM produtos p JOIN categorias c ON c.id = p.categoria_id;

SELECT rowid, nome FROM busca WHERE busca MATCH '*eira';

A mensagem: unknown special query: eira, "consulta especial desconhecida". Um * no começo da busca é outra coisa no FTS5 (um comando especial), e "eira" não é um comando. O índice é organizado pelo começo das palavras; "palavras que terminam com" ele não sabe procurar.

Como consertar: use o tokenizador trigram (seção 6), que acha pedaços em qualquer posição.

Tipo na coluna, ou FTS4

CREATE VIRTUAL TABLE busca USING fts5(nome TEXT, categoria TEXT);

A mensagem: unrecognized column option: TEXT, "opção de coluna desconhecida". No FTS5, a coluna é só o nome. Tire o TEXT.

Em tutoriais antigos você vai achar USING fts4 (ou fts3), as versões anteriores. No SQLite deste guia, elas não vêm: CREATE VIRTUAL TABLE x USING fts4(a); dá no such module: fts4, "não existe o módulo fts4". Use o fts5.

Ordenou com DESC

Quem vem de outros buscadores acha que nota maior é melhor e escreve ORDER BY rank DESC. Não dá erro, mas a lista sai de trás para frente: os piores primeiro. No FTS5 a nota é negativa e a menor é a melhor. Use ORDER BY rank, sem DESC.

Nos outros bancos

Cada banco tem a sua busca de texto, com outra escrita. Os trechos abaixo são ilustrativos: não rodam no SQLite.

MySQL: um índice FULLTEXT na tabela comum (só nas tabelas InnoDB ou MyISAM) e a busca com MATCH (colunas) AGAINST ('texto').
PostgreSQL: o texto vira um tsvector com to_tsvector(), a busca vira um tsquery com to_tsquery(), e o operador @@ compara os dois. A nota vem de ts_rank() e o destaque, de ts_headline(). Ali, nota maior é melhor.
SQL Server: um índice de texto completo (full-text index) e as condições CONTAINS() e FREETEXT(); a nota vem da coluna RANK de CONTAINSTABLE(), e lá também maior é melhor.

-- MySQL (ilustrativo)
SELECT nome FROM produtos WHERE MATCH (nome) AGAINST ('azul');

-- PostgreSQL (ilustrativo)
SELECT nome FROM produtos
WHERE to_tsvector('portuguese', nome) @@ to_tsquery('portuguese', 'azul');

-- SQL Server (ilustrativo)
SELECT nome FROM produtos WHERE CONTAINS (nome, 'azul');

Referência rápida

Escrita	O que faz
`CREATE VIRTUAL TABLE t USING fts5(a, b)`	Cria a tabela de busca com as colunas `a` e `b` (sem tipos)
`… fts5(a, tokenize = 'unicode61 remove_diacritics 2')`	Tira todos os acentos do índice
`… fts5(a, tokenize = 'trigram')`	Índice por grupos de 3 letras: acha pedaços
`WHERE t MATCH 'azul'`	Linhas com a palavra, em qualquer coluna
`WHERE a MATCH 'azul'` / `MATCH 'a: azul'`	Só na coluna `a`
`'x y'` ou `'x AND y'`	As duas palavras
`'x OR y'` / `'x NOT y'`	Uma das duas / a primeira sem a segunda (operadores em MAIÚSCULAS)
`'"de cozinha"'`	Frase exata, nessa ordem
`'cad*'`	Palavras que começam com "cad"
`'NEAR(x y, 3)'`	As duas com até 3 palavras entre elas (padrão: 10)
`ORDER BY rank`	Mais relevantes primeiro (nota menor = melhor)
`bm25(t, 10.0, 1.0)`	A nota com pesos por coluna
`highlight(t, col, 'antes', 'depois')`	O texto da coluna com as palavras achadas marcadas
`snippet(t, col, 'antes', 'depois', '…', n)`	Um trecho de até `n` palavras em volta do que achou

Mensagem do SQLite	Quer dizer
`no such column: texto`	Um hífen ou dois-pontos na busca virou filtro de coluna: ponha o termo entre aspas duplas
`unable to use function MATCH in the requested context`	`MATCH` numa tabela que não é FTS5
`fts5: syntax error near "…"`	A busca está mal escrita (operador sem palavra, aspas sem fechar…)
`unknown special query: …`	A busca começa com `*`
`unrecognized column option: TEXT`	Tipo na coluna do FTS5
`no such module: fts4`	Este SQLite não tem o FTS4: use o FTS5

Exercícios

1. Criar a busca

Complete para criar uma tabela de busca com a coluna titulo:

CREATE VIRTUAL TABLE livros_busca USING (titulo);

2. Começo de palavra

Complete a busca para achar "Jardim" e "Jardinagem" pelo começo comum:

SELECT nome FROM busca WHERE busca MATCH '';

3. Qual ordem mostra os resultados mais relevantes primeiro no FTS5?

ORDER BY rank DESC ORDER BY rowid ORDER BY rank Não precisa: o FTS5 já devolve em ordem de relevância

4. A busca MATCH 'jogo or panelas' volta vazia. Por quê?

Porque OR não existe no FTS5 Porque o or minúsculo virou uma palavra a procurar, e nenhum produto tem "or" Porque faltaram parênteses Porque "panelas" tem acento

5. O cliente digitou usb-c e o SQLite respondeu no such column: c. Qual é o conserto?

Criar uma coluna c na tabela de busca Trocar o MATCH por = Usar remove_diacritics 2 Pôr o termo entre aspas duplas: MATCH '"usb-c"'

6. Com o tokenizador padrão do FTS5, a busca 'ceramica' acha "Vaso de Cerâmica Médio"?

Sim: o padrão (remove_diacritics 1) já tira os acentos do português Não: só com remove_diacritics 0 Não: precisa escrever 'cerâmica' Só com o LIKE

Missão final: a busca que ignora acentos

Missão: alguém criou a busca com remove_diacritics 0, e agora o cliente que digita "ceramica" não acha nada. Conserte o CREATE para tirar todos os acentos (use o valor recomendado) e troque a coluna nome do SELECT por um destaque da palavra encontrada entre [ e ].

CREATE VIRTUAL TABLE busca USING fts5(nome, tokenize = 'unicode61 remove_diacritics 0');
INSERT INTO busca (rowid, nome) SELECT id, nome FROM produtos;

SELECT rowid, nome
FROM busca
WHERE busca MATCH 'ceramica'
ORDER BY rank;

CREATE VIRTUAL TABLE busca USING fts5(nome, tokenize = 'unicode61 remove_diacritics 2');
INSERT INTO busca (rowid, nome) SELECT id, nome FROM produtos;

SELECT rowid, highlight(busca, 0, '[', ']') AS produto
FROM busca
WHERE busca MATCH 'ceramica'
ORDER BY rank;

Resumo

A busca de texto completo é um índice de palavras, como o índice remissivo de um livro. No SQLite, é o FTS5.
Crie com CREATE VIRTUAL TABLE busca USING fts5(col1, col2) (sem tipos) e copie os dados com INSERT … SELECT, guardando o id no rowid.
Busque com WHERE busca MATCH '…': palavras (todas), OR, NOT (em MAIÚSCULAS), "frase", cad*, nome: ….
ORDER BY rank mostra os melhores primeiro (nota menor = melhor); bm25(t, pesos…) dá peso às colunas.
highlight() marca as palavras achadas; snippet() mostra um trecho em volta.
O tokenizador unicode61 ignora maiúsculas e acentos; prefira tokenize = 'unicode61 remove_diacritics 2'. Para pedaços do meio da palavra, trigram.
A tabela de busca é uma cópia: quando o produto muda, a cópia tem de mudar junto. Ponha entre aspas duplas o que o cliente digitar, para o hífen não virar erro.