Realizar busca de texto completo - AnalyticDB: Central de Documentação do Alibaba Cloud

O AnalyticDB for MySQL oferece suporte à busca de texto completo por meio de três funções SQL: MATCH() AGAINST(), MATCH() FUZZY() e MATCH() PHRASE(). Use essas funções para pesquisar colunas de texto, classificar resultados por relevância e destacar palavras-chave correspondentes.

A busca de texto completo permite:

Pesquisar em uma ou mais colunas de texto sem criar índices de múltiplas colunas
Classificar resultados por pontuação de relevância e filtrar correspondências de baixa relevância
Lidar com erros de ortografia usando correspondência difusa baseada na distância de edição
Corresponder frases de várias palavras com tolerância posicional usando slop
Destacar palavras-chave correspondentes nos resultados da consulta usando tags HTML personalizadas

Pré-requisitos

Antes de começar, verifique se:

Você criou índices de texto completo nas colunas que deseja pesquisar. Para obter instruções, consulte Criar um índice de texto completo

Dados de exemplo

Os exemplos neste tópico usam uma tabela chamada tbl_fulltext_demo. Cada coluna possui um índice de texto completo com um analisador diferente.

Execute as seguintes instruções para criar a tabela e inserir dados de exemplo:

/* RC_DDL_ENGINE_REWRITE_XUANWUV2=false */
CREATE TABLE `tbl_fulltext_demo` (
  `id` INT,
  `content` VARCHAR,
  `content_alinlp` VARCHAR,
  `content_ik` VARCHAR,
  `content_standard` VARCHAR,
  `content_ngram` VARCHAR,
  FULLTEXT INDEX fidx_c(`content`),              -- Default analyzer
  FULLTEXT INDEX fidx_alinlp(`content_alinlp`) WITH ANALYZER alinlp,  -- AliNLP analyzer
  FULLTEXT INDEX fidx_ik(`content_ik`) WITH ANALYZER ik,              -- IK analyzer
  FULLTEXT INDEX fidx_standard(`content_standard`) WITH ANALYZER standard, -- Standard analyzer
  FULLTEXT INDEX fidx_ngram(`content_ngram`) WITH ANALYZER ngram,     -- Ngram analyzer
  PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(id);

INSERT INTO tbl_fulltext_demo(id, content, content_alinlp, content_ik, content_standard, content_ngram)
VALUES
  (1, 'Customers Need Better Products and Services', 'Customers Need Better Products and Services', 'Customers Need Better Products and Services', 'Customers Need Better Products and Services', 'Customers Need Better Products and Services'),
  (2, 'Wuhan Changjiang Bridge', 'Wuhan Changjiang Bridge', 'Wuhan Changjiang Bridge', 'Wuhan Changjiang Bridge', 'Wuhan Changjiang Bridge'),
  (3, 'Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province'),
  (4, 'User Values and Commercial Values of Products', 'User Values and Commercial Values of Products', 'User Values and Commercial Values of Products', 'User Values and Commercial Values of Products', 'User Values and Commercial Values of Products');

Observações de uso

As funções de índice de texto completo aceitam os seguintes caracteres especiais, mas você deve escapá-los com \\: +-&|!(){}[]^"~*?:\/ Por exemplo, para pesquisar Spring/Scenery:
- Incorreto: MATCH(content) AGAINST('Spring / Scenery')
- Correto: MATCH(content) AGAINST('Spring \\/ Scenery')
As funções de índice de texto completo não suportam operadores como =, !=, BETWEEN, IS NULL, IS NOT NULL e LIKE.

Escolha uma função

Função	Quando usar
`MATCH() AGAINST()`	Para buscar palavras-chave exatas ou correspondentes, com lógica booleana opcional
`MATCH() FUZZY()`	Quando as palavras-chave podem conter erros de digitação ou serem aproximadas
`MATCH() PHRASE()`	Quando múltiplas palavras-chave devem aparecer juntas, com tolerância posicional

MATCH() AGAINST()

A função MATCH() AGAINST() pesquisa linhas em uma ou mais colunas que correspondam a uma palavra-chave. Ela suporta correspondência de palavras (padrão) e correspondência exata (com palavras-chave entre aspas), além de consultas booleanas com operadores lógicos.

Sintaxe

SELECT * FROM `table_name` WHERE MATCH (column_name[, ...]) AGAINST('term')

Parâmetros

Parâmetro	Descrição
`table_name`	A tabela a ser pesquisada
`column_name`	A coluna a ser pesquisada. Separe múltiplas colunas com vírgulas
`term`	A palavra-chave de busca. Suporta os operadores lógicos `AND`, `OR` e `NOT` (não diferencia maiúsculas de minúsculas)

Operadores lógicos em term:

Operador	Comportamento
`AND`	Retorna linhas que correspondem a todas as palavras-chave
`OR`	Retorna linhas que correspondem a qualquer palavra-chave
`NOT`	Retorna linhas que correspondem à palavra-chave à esquerda, mas não à palavra-chave à direita

Pontuações de relevância

A função MATCH() AGAINST() atribui uma pontuação de relevância a cada resultado. Pontuações mais altas indicam correspondências mais fortes.

Use MATCH() AGAINST() > threshold na cláusula WHERE para filtrar resultados por pontuação. Por exemplo, > 0.9 retorna apenas os 10% melhores resultados por relevância.

Os resultados não são classificados por pontuação de relevância por padrão. Use ORDER BY score DESC para classificar explicitamente.

Exemplo 1: Consulta em coluna única

SELECT id, content
FROM `tbl_fulltext_demo`
WHERE MATCH(`content`) AGAINST('Products Services');

Resultado:

+------+-----------------------------------------------+
| id   | content                                       |
+------+-----------------------------------------------+
| 4    | User Values and Commercial Values of Products |
| 1    | Customers Need Better Products and Services   |
+------+-----------------------------------------------+

Exemplo 2: Consulta em múltiplas colunas

Para pesquisar em várias colunas, cada uma deve ter seu próprio índice de texto completo. Não é necessário um índice de múltiplas colunas.

SELECT id, content, content_alinlp
FROM `tbl_fulltext_demo`
WHERE MATCH(content, content_alinlp) AGAINST('Services');

A seguinte consulta é equivalente:

SELECT id, content, content_alinlp
FROM `tbl_fulltext_demo`
WHERE MATCH(content) AGAINST('Services')
   OR MATCH(content_alinlp) AGAINST('Services');

Resultado:

+------+---------------------------------------------+---------------------------------------------+
| id   | content                                     | content_alinlp                              |
+------+---------------------------------------------+---------------------------------------------+
| 1    | Customers Need Better Products and Services | Customers Need Better Products and Services |
+------+---------------------------------------------+---------------------------------------------+

Exemplo 3: Consulta booleana

Use operadores lógicos para construir consultas booleanas.

AND — corresponde a todas as palavras-chave:

SELECT * FROM `tbl_fulltext_demo`
WHERE MATCH(content) AGAINST('Products AND Services');

OR — corresponde a qualquer palavra-chave:

SELECT * FROM `tbl_fulltext_demo`
WHERE MATCH(content) AGAINST('Products OR Services');

NOT — corresponde à palavra-chave da esquerda, mas exclui a da direita:

SELECT * FROM `tbl_fulltext_demo`
WHERE MATCH(content) AGAINST('Products NOT Services');

NOT com curinga — exclui uma palavra-chave de todas as linhas:

SELECT * FROM `tbl_fulltext_demo`
WHERE MATCH(content) AGAINST('*:* NOT Services');

Importante

*:* corresponde a todas as linhas. Ele só pode ser usado à esquerda de NOT e deve aparecer no início do valor term.

Parênteses — constroem consultas booleanas complexas:

SELECT id, content_alinlp
FROM `tbl_fulltext_demo`
WHERE MATCH(content_alinlp) AGAINST('(Wuhan OR Hangzhou) AND (Bridge OR Xihu)');

Resultado:

+------+---------------------+
| id   | content_alinlp      |
+------+---------------------+
| 2    | Wuhan Changjiang Bridge |
+------+---------------------+

Exemplo 4: Filtrar por pontuação de relevância

Para retornar apenas os resultados mais relevantes, adicione um limiar de pontuação à cláusula WHERE. A consulta a seguir filtra os 90% piores resultados por relevância e retorna os 10% melhores:

SELECT id, content
FROM `tbl_fulltext_demo`
WHERE MATCH(content) AGAINST('Products Services') > 0.9;

Resultado:

+------+-----------------------------------------------+
| id   | content                                       |
+------+-----------------------------------------------+
| 4    | User Values and Commercial Values of Products |
| 1    | Customers Need Better Products and Services   |
+------+-----------------------------------------------+

Exemplo 5: Classificar resultados por pontuação de relevância

Para recuperar e classificar por pontuações de relevância, inclua MATCH() AGAINST() na projeção SELECT e use ORDER BY.

SELECT id, content, MATCH(content) AGAINST('Products Services') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH(content) AGAINST('Products Services') > 0.9
ORDER BY score DESC;

Resultado:

+------+-----------------------------------------------+--------------------+
| id   | content                                       | score              |
+------+-----------------------------------------------+--------------------+
| 1    | Customers Need Better Products and Services   | 0.2615291476249695 |
| 4    | User Values and Commercial Values of Products | 0.13076457381248474 |
+------+-----------------------------------------------+--------------------+

A expressão MATCH() AGAINST() no SELECT e aquela no WHERE podem usar palavras-chave diferentes. No exemplo a seguir, a cláusula WHERE filtra por 'Products Services' , enquanto a projeção SELECT calcula a pontuação por 'Wuhan' :

SELECT *, MATCH(content) AGAINST('Wuhan') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH(content) AGAINST('Products Services') > 0.9
ORDER BY score DESC;

Exemplo 6: Correspondência de palavras

Por padrão, o analisador segmenta a palavra-chave em palavras individuais antes de pesquisar. As linhas que correspondem a qualquer uma das palavras são retornadas.

O analisador AliNLP segmenta 'Products Services' em Products e Services. A consulta a seguir retorna linhas que contêm qualquer uma das palavras:

SELECT id, content_alinlp, MATCH(content_alinlp) AGAINST('Products Services') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH(content_alinlp) AGAINST('Products Services') > 0.9
ORDER BY score DESC;

Resultado:

+------+-----------------------------------------------+--------------------+
| id   | content_alinlp                                | score              |
+------+-----------------------------------------------+--------------------+
| 1    | Customers Need Better Products and Services   | 0.2615291476249695 |
| 4    | User Values and Commercial Values of Products | 0.13076457381248474 |
+------+-----------------------------------------------+--------------------+

Exemplo 7: Correspondência exata

Coloque a palavra-chave entre aspas duplas (") para desativar a segmentação de palavras. Apenas as linhas que correspondem exatamente à frase completa da palavra-chave serão retornadas.

Pesquise pela frase exata "product service" (nenhuma correspondência esperada):

SELECT id, content_alinlp, MATCH(content_alinlp) AGAINST('"product service"') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH(content_alinlp) AGAINST('"product service"') > 0.9
ORDER BY score DESC;

Resultado: Empty set

Pesquise pela frase exata "Products and Services":

SELECT id, content_alinlp, MATCH(content_alinlp) AGAINST('"Products and Services"') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH(content_alinlp) AGAINST('"Products and Services"') > 0.9
ORDER BY score DESC;

Resultado:

+------+-----------------------------------------------+--------------------+
| id   | content_alinlp                                | score              |
+------+-----------------------------------------------+--------------------+
| 1    | Customers Need Better Products and Services   | 0.3922937512397766 |
+------+-----------------------------------------------+--------------------+

MATCH() FUZZY()

A função MATCH() FUZZY() pesquisa linhas onde o texto indexado está dentro de uma distância de edição Levenshtein especificada da palavra-chave. Isso é útil quando a palavra-chave de busca pode conter erros de ortografia.

Sintaxe

SELECT * FROM `table_name` WHERE MATCH (`column_name`) FUZZY('term') [max_edits(n)]

Parâmetros

Parâmetro	Descrição
`table_name`	A tabela a ser pesquisada
`column_name`	A coluna a ser pesquisada
`term`	A palavra-chave de busca
`max_edits(n)`	(Opcional) A distância máxima de edição. Uma edição é uma única inserção, exclusão ou substituição. Padrão: `2`. Valores válidos: `0`, `1`, `2`. Por exemplo, alterar `windos` para `windows` requer uma inserção, portanto a distância de edição é `1`.

Exemplo 1: Busca difusa com distância de edição padrão

Pesquise por hangzou (ortografia incorreta), que corresponde a Hangzhou dentro de 2 edições:

SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('hangzou');

Resultado:

+------+-----------------------------+
| id   | content                     |
+------+-----------------------------+
| 3    | Hangzhou, Zhejiang Province |
+------+-----------------------------+

Exemplo 2: Busca difusa com max_edits(1)

Limite as correspondências a 1 edição:

SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('hangzou') max_edits(1);

Resultado:

+------+-----------------------------+
| id   | content                     |
+------+-----------------------------+
| 3    | Hangzhou, Zhejiang Province |
+------+-----------------------------+

Exemplo 3: Busca difusa com max_edits(2)

SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('Wuhan Chang') max_edits(2);

Resultado:

+------+-----------------------+
| id   | content               |
+------+-----------------------+
| 2    | Wuhan Changjiang Bridge |
+------+-----------------------+

MATCH() PHRASE()

A função MATCH() PHRASE() pesquisa linhas onde uma coluna específica contém uma sequência de palavras-chave em uma determinada ordem. Use o parâmetro slop para permitir flexibilidade posicional entre as palavras-chave.

Sintaxe

SELECT * FROM `table_name` WHERE MATCH (`column_name`) PHRASE('term1 term2') [slop(n)]

Parâmetros

Parâmetro	Descrição
`table_name`	A tabela a ser pesquisada
`column_name`	A coluna a ser pesquisada
`term1 term2`	As palavras-chave a serem correspondidas, separadas por espaços. A ordem das palavras-chave afeta os resultados
`slop(n)`	(Opcional) O número máximo de movimentos posicionais permitidos para corresponder à sequência de palavras-chave. Padrão: `0`. Valores válidos: inteiros de `0` a `6`.

Entendendo o slop:

Após a tokenização, cada palavra recebe uma posição começando em 0. O valor de slop especifica quantas posições uma palavra pode ser movida para corresponder à sequência especificada.

Por exemplo, o analisador Standard tokeniza "Merchandise and Services" como:

Palavra	Posição
Merchandise	0
and	1
Services	2

PHRASE('Merchandise and'): a distância de slop é 1 (mover uma posição para alinhar)
PHRASE('Merchandise Services'): a distância de slop é 2 (pular and)

Exemplo 1: Busca de frases

O analisador Standard tokeniza "Hangzhou, Zhejiang Province" em Hangzhou, Zhejiang e Province.

Pesquise por zhejiang hangzhou (ordem invertida, slop 0 — nenhuma correspondência esperada):

SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) PHRASE('zhejiang hangzhou');

Resultado: Empty set

Pesquise por hangzhou Province com slop 1 (uma palavra entre elas):

SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) PHRASE('hangzhou Province') slop(1);

Resultado:

+------+-----------------------------+
| id   | content                     |
+------+-----------------------------+
| 3    | Hangzhou, Zhejiang Province |
+------+-----------------------------+

Pesquise por zhejiang hangzhou com slop 2 (ordem invertida, dentro de 2 movimentos):

SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) PHRASE('zhejiang hangzhou') slop(2);

Resultado:

+------+-----------------------------+
| id   | content                     |
+------+-----------------------------+
| 3    | Hangzhou, Zhejiang Province |
+------+-----------------------------+

Exemplo 2: Busca de frases com texto em chinês

Em cenários de correspondência de frases em chinês, use o analisador Standard ou o analisador IK. O analisador AliNLP e o analisador Ngram podem produzir resultados inesperados.

Pesquise por Products Services com slop 1 usando o analisador IK:

SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_ik) PHRASE('Products Services') slop(1);

Resultado:

+------+---------------------------------------------+
| id   | content                                     |
+------+---------------------------------------------+
| 1    | Customers Need Better Products and Services |
+------+---------------------------------------------+

Pesquise por Products Services com slop 2 usando o analisador Standard:

SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) PHRASE('Products Services') slop(2);

Resultado:

+------+---------------------------------------------+
| id   | content                                     |
+------+---------------------------------------------+
| 1    | Customers Need Better Products and Services |
+------+---------------------------------------------+

Destacar palavras-chave

Use a função fulltext_highlight(column_name) para destacar as palavras-chave correspondentes no resultado. Por padrão, os termos correspondentes são envolvidos pelas tags <em> e </em>.

Usar fulltext_highlight() com MATCH() AGAINST()

Consulta em coluna única com destaque de palavras-chave:

SELECT MATCH(content_alinlp) AGAINST('Wuhan Changjiang') AS score,
       fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH(content_alinlp) AGAINST('Wuhan Changjiang') > 0.9
ORDER BY score DESC
LIMIT 3;

Importante

WHERE MATCH(content_alinlp) AGAINST('Wuhan Changjiang') > 0.9 filtra os 90% piores resultados por relevância e retorna os 10% melhores.

Resultado:

+--------------------+-----------------------------------------+
| score              | fulltext_highlight(content_alinlp)      |
+--------------------+-----------------------------------------+
| 0.4413304328918457 | <em>Wuhan</em> <em>Changjiang</em> Bridge |
+--------------------+-----------------------------------------+

Consulta em múltiplas colunas com destaque de palavras-chave:

SELECT MATCH(content_alinlp) AGAINST('Wuhan Changjiang') AS score,
       fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH(content_alinlp) AGAINST('Wuhan Changjiang') > 0.9
  AND MATCH(content_alinlp) AGAINST('Bridge') > 0.9
ORDER BY score DESC
LIMIT 3;

Usar fulltext_highlight() com MATCH() FUZZY()

SELECT id,
       MATCH(content_standard) FUZZY('Wuhan Chang') AS score,
       fulltext_highlight(content_standard)
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('Wuhan Chang');

Resultado:

+------+-------+--------------------------------------------------+
| id   | score | fulltext_highlight(content_standard)             |
+------+-------+--------------------------------------------------+
| 2    | 0.0   | <em>Wuhan</em> Chang<em>jiang</em> Bridge        |
+------+-------+--------------------------------------------------+

Usar fulltext_highlight() com MATCH() PHRASE()

SELECT id,
       MATCH(content_ik) PHRASE('Products Services') slop(1) AS score,
       fulltext_highlight(content_ik)
FROM tbl_fulltext_demo
WHERE MATCH(content_ik) PHRASE('Products Services') slop(1);

Resultado:

+------+--------------------+------------------------------------------------------------------+
| id   | score              | fulltext_highlight(content_ik)                                   |
+------+--------------------+------------------------------------------------------------------+
| 1    | 0.1692247390747070 | Customers Need Better <em>Products</em> and <em>Services</em>    |
+------+--------------------+------------------------------------------------------------------+

Adicionar tags de destaque personalizadas

Para usar tags HTML personalizadas em vez de <em> e </em>, defina fulltext_highlight_pre_tag e fulltext_highlight_post_tag em uma dica de consulta.

Com MATCH() AGAINST():

/*+ fulltext_highlight_pre_tag=<strong> fulltext_highlight_post_tag=</strong> */
SELECT MATCH(content_alinlp) AGAINST('Wuhan Changjiang') AS score,
       fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH(content_alinlp) AGAINST('Wuhan Changjiang') > 0.9
ORDER BY score DESC
LIMIT 3;

Importante

WHERE MATCH(content_alinlp) AGAINST('Wuhan Changjiang') > 0.9 filtra os 90% piores resultados por relevância e retorna os 10% melhores.

Resultado:

+--------------------+-------------------------------------------------+
| score              | fulltext_highlight(content_alinlp)              |
+--------------------+-------------------------------------------------+
| 0.2615291476249695 | <strong>Wuhan</strong> <strong>Changjiang</strong> Bridge |
+--------------------+-------------------------------------------------+

Com MATCH() FUZZY():

/*+ fulltext_highlight_pre_tag=<strong> fulltext_highlight_post_tag=</strong> */
SELECT MATCH(content_alinlp) FUZZY('Wuhan Changjiang') AS score,
       fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH(content_alinlp) FUZZY('Wuhan Changjiang') > 0.9
ORDER BY score DESC
LIMIT 3;

Importante

WHERE MATCH(content_alinlp) FUZZY('Wuhan Changjiang') > 0.9 filtra os 90% piores resultados por relevância e retorna os 10% melhores.

Resultado:

+--------------------+-------------------------------------------------+
| score              | fulltext_highlight(content_alinlp)              |
+--------------------+-------------------------------------------------+
| 0.0                | <strong>Wuhan</strong> Chang<strong>jiang</strong> Bridge |
+--------------------+-------------------------------------------------+

Com MATCH() PHRASE():

/*+ fulltext_highlight_pre_tag=<strong> fulltext_highlight_post_tag=</strong> */
SELECT MATCH(content_alinlp) PHRASE('Wuhan') AS score,
       fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH(content_alinlp) PHRASE('Wuhan') > 0.9
ORDER BY score DESC
LIMIT 3;

Importante

WHERE MATCH(content_alinlp) PHRASE('Wuhan') > 0.9 filtra os 90% piores resultados por relevância e retorna os 10% melhores.

Resultado:

+---------------------+---------------------------------------------------------------+
| score               | fulltext_highlight(content_alinlp)                            |
+---------------------+---------------------------------------------------------------+
| 0.13076457381248474 | <strong>Wuhan</strong> Changjiang Bridge                      |
+---------------------+---------------------------------------------------------------+