Resumo : neste tutorial, você aprenderá como usar o recurso de pesquisa de texto completo SQLite usando o módulo de tabela virtual FTS5.
Introdução à pesquisa de texto completo SQLite
Uma tabela virtual é uma extensão personalizada do SQLite. Uma mesa virtual é como uma mesa normal. A diferença entre uma tabela virtual e uma tabela normal é de onde vêm os dados, ou seja, quando você processa uma tabela normal, o SQLite acessa o arquivo do banco de dados para recuperar os dados. Porém, quando você acessa uma tabela virtual, o SQLite chama o código customizado para obter os dados. O código personalizado pode ter lógica especificada para lidar com determinadas tarefas, como obter dados de diversas fontes de dados.
Para usar a pesquisa de texto completo no SQLite, você usa o módulo de tabela virtual FTS5.
A CREATE VIRTUAL TABLEinstrução a seguir cria uma tabela FTS5 com duas colunas:
CREATE VIRTUAL TABLE table_name
USING FTS5(column1,column2...);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Observe que você não pode adicionar tipos, restrições ou PRIMARY KEYdeclarações na CREATE VIRTUAL TABLEinstrução para criar uma tabela FTS5. Se você fizer isso, o SQLite emitirá um erro.
Assim como criar uma tabela normal sem especificar a coluna de chave primária , o SQLite adiciona uma rowidcoluna implícita à tabela FTS5.
O exemplo a seguir cria uma tabela FTS5 nomeada postscom duas colunas titlee body.
CREATE VIRTUAL TABLE posts
USING FTS5(title, body);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Semelhante a uma tabela normal, você pode inserir dados na poststabela da seguinte forma:
INSERT INTO posts(title,body)
VALUES('Learn SQlite FTS5','This tutorial teaches you how to perform full-text search in SQLite using FTS5'),
('Advanced SQlite Full-text Search','Show you some advanced techniques in SQLite full-text searching'),
('SQLite Tutorial','Help you learn SQLite quickly and effectively');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )E consulte os dados:
SELECT * FROM posts;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Consultando dados usando pesquisa de texto completo
Você pode executar uma consulta de texto completo em uma tabela FTS5 usando uma destas três maneiras.
Primeiro, use um MATCHoperador na cláusula WHERE da instrução SELECT . Por exemplo, para obter todas as linhas que possuem o termo fts5, use a seguinte consulta:
SELECT *
FROM posts
WHERE posts MATCH 'fts5';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, use um =operador igual ( ) na WHEREcláusula da SELECTinstrução. A instrução a seguir retorna o mesmo resultado da instrução acima:
SELECT *
FROM posts
WHERE posts = 'fts5';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, use uma sintaxe de função de valor tabelado. Desta forma, você usa o termo de pesquisa como o primeiro argumento da tabela:
SELECT *
FROM posts('fts5');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Por padrão, o FTS5 é independente de maiúsculas e minúsculas. Trata os termos fts5FTS5e Fts5o mesmo.
Para classificar os resultados da pesquisa do mais para o menos relevante, use a cláusula ORDER BY da seguinte forma:
SELECT *
FROM posts
WHERE posts MATCH 'text'
ORDER BY rank;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Usando sintaxe de consulta de texto completo
Uma consulta de pesquisa de texto completo é composta de frases, onde cada frase é uma lista ordenada de um ou mais tokens. Você pode usar o operador “+” para concatenar duas frases como no exemplo a seguir:
"learn SQLite"
"learn + SQLite"
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O FTS5 determina se um documento corresponde a uma frase se o documento contiver pelo menos uma subsequência de tokens que corresponda à sequência de tokens usada para construir a frase.
A consulta a seguir retorna todos os documentos que correspondem ao termo de pesquisa Learn SQLite:
SELECT *
FROM posts
WHERE posts MATCH 'learn SQLite';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Pesquisas de prefixo
Você pode usar o asterisco (*) como token de prefixo. Quando uma frase contém o asterisco (*), ela corresponderá a qualquer documento que contenha o token que começa com a frase. Por exemplo, search* corresponde a search, search, search, etc. Veja o exemplo a seguir:
SELECT *
FROM posts
WHERE posts = 'search*';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Operadores booleanos
Você pode usar o operador booleano, por exemplo NOT, OR, ou ANDpara combinar consultas.
- q1 AND q2: corresponde se as consultas q1 e q2 corresponderem.
- q1 OR q2: corresponde se a consulta q1 ou q2 corresponder.
- q1 NOT q2: corresponde se a consulta q1 corresponder e q2 não corresponder.
Por exemplo, para obter os documentos que correspondem à learnfrase, mas não correspondem à FTS5frase, use o NOToperador da seguinte forma:
SELECT *
FROM posts
WHERE posts MATCH 'learn NOT text';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para pesquisar documentos que correspondam à frase learnou text, use o ORoperador como no exemplo a seguir:
SELECT *
FROM posts
WHERE posts MATCH 'learn OR text';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para encontrar os documentos que correspondem ao SQLite e à pesquisa, você usa o ANDoperador conforme mostrado abaixo:
SELECT *
FROM posts
WHERE posts MATCH 'sqlite AND searching';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para alterar a precedência do operador, use parênteses para agrupar expressões. Por exemplo:
SELECT *
FROM posts
WHERE posts MATCH 'search AND sqlite OR help';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A instrução retorna documentos que correspondem searcha sqliteand or help. Para encontrar os documentos que correspondem searcha sqliteou ou help, use parênteses da seguinte maneira:
SELECT *
FROM posts
WHERE posts MATCH 'search AND (sqlite OR help)';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Funções auxiliares integradas
SQLite fornece três funções auxiliares integradas que podem ser usadas em consultas de texto completo na tabela FTS5.
- O
bm25()retorna um valor que representa a precisão da correspondência atual; o valor mais baixo significa uma correspondência melhor. - A
highlight()função auxiliar retorna uma cópia do texto com os termos de pesquisa cercados por uma marcação especificada, por exemplo,<b>termo de pesquisa</b> - O
snippet()seleciona um pequeno fragmento de texto para maximizar o número de termos de pesquisa que ele contém.
Por exemplo, a consulta a seguir usa a função destaque() para decorar os termos de pesquisa usando a <b>tag</b> :
SELECT highlight(posts,0, '<b>', '</b>') title,
highlight(posts,1, '<b>', '</b>') body
FROM posts
WHERE posts MATCH 'SQLite'
ORDER BY rank;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste tutorial, você aprendeu como usar os recursos de pesquisa de texto completo do SQLite por meio do módulo de tabela virtual FTS5.
