Introdução a buscas via FULLTEXT no MySQL – Elias Praciano
Categories
Banco de dados Tutoriais

Introdução a buscas via FULLTEXT no MySQL

Como usar índices FULLTEXT no MySQL para fazer buscas eficientes e encontrar strings em um grande volume de dados.

Usar o casamento de padrões para encontrar informações dentro dos campos de uma tabela é eficiente, como foi demonstrado no post Como encontrar padrões entre os seus dados no MySQL. À medida em que o volume de informação (ou de texto) aumenta, a operação de busca vai se tornar mais lenta.
MySQL logo Dolphin in gray shades
A tarefa de buscar um mesmo trecho dentro de várias strings é também muito comum — e não é muito eficiente através do casamento de padrões — principalmente em bancos de dados volumosos.

SELECT * FROM clientesCidades WHERE coluna1 LIKE 'padrao' OR  coluna1 LIKE 'padrao' OR  coluna2 LIKE 'padrao OR coluna3 LIKE 'padrao'' ... 

Imagine fazer isto para 10 colunas ou mais.
Uma alternativa melhor é usar a busca FULLTEXT, projetada para sondar grandes volumes de texto — em múltiplas colunas simultaneamente.
Para poder usar este recurso, é necessário adicionar o índice FULLTEXT à sua tabela.
Feito isto, é possível usar o operador MATCH para procurar por strings dentro das colunas.
Tabelas MYISAM podem fazer uso do recurso FULLTEXT, nos campos do tipo CHAR, VARCHAR ou TEXT.
A melhor forma de criar exemplos de uso do FULLTEXT é usar uma tabela com uma grande quantidade de texto. Se você não tiver uma disponível, tente encontrar algum repositório na Internet com informações em texto.
Nos exemplos, que seguem, vou usar a bíblia em modo texto, que baixei daqui — pelo fato de ela estar muito bem organizada e ter uma grande quantidade de texto para nosso uso.
Vejo um trecho-exemplo:

livro original capítulo original versículo original texto
010 5 7 Viveu Sete, depois que gerou a Enos, oitocentos e sete anos; e gerou filhos e filhas.
010 5 8 Todos os dias de Sete foram novecentos e doze anos; e morreu.
010 5 9 Enos viveu noventa anos, e gerou a Quenã.

Você pode usar qualquer outra tabela que tiver disponível, com uma grande quantidade de texto. Só não esqueça de adequar os exemplos deste tutorial ao seu caso.

Como importar dados em texto pro MySQL

Notou que cada coluna pode ser um campo para uma tabela no banco de dados MySQL?
Que campos são estes?
Cada registro, pode ser compartimentado sob as seguintes colunas:

  1. O número do livro: 010, 020 etc.;
  2. O número do capítulo;
  3. O número do versículo e
  4. O texto do versículo.

—Os números dos livros correspondem aos nomes (em outra tabela): 010 – Gênesis, 020 – Exodus etc.
Esta relação não será abordada neste tutorial.


Antes de prosseguir, vou criar uma tabela para receber os dados do texto.
Dentro do console MySQL, crie uma tabela da seguinte forma:

CREATE TABLE biblia
(
numliv TINYINT UNSIGNED NOT NULL, 
numcap TINYINT UNSIGNED NOT NULL, 
numver TINYINT UNSIGNED NOT NULL, 
textover TEXT NOT NULL 
)
TYPE = MyISAM;

Verifique a tabela:

EXPLAIN biblia;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| numliv   | tinyint(3) unsigned | NO   |     | NULL    |       |
| numcap   | tinyint(3) unsigned | NO   |     | NULL    |       |
| numver   | tinyint(3) unsigned | NO   |     | NULL    |       |
| textover | text                | NO   |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Feito isto, importe o arquivo informando seu nome e o caminho completo através da declaração LOAD DATA LOCAL INFILE, que funciona assim:

LOAD DATA LOCAL INFILE '/diretorio/arquivo.txt' INTO TABLE nome-da-tabela;

No meu exemplo, o comando ficou assim:

LOAD DATA LOCAL INFILE '/home/henry/Downloads/KJV/port/biblia.txt' INTO TABLE biblia;

Se você se deparar com a mensagem de erro The used command is not allowed with this MySQL version, leia o artigo Como resolver o erro The used command…


Se você tiver algum outro problema neste processo, tente encontrar ajuda em outros artigos neste site (use o quadro de busca) ou em outros — neste post vamos nos concentrar no uso do FULLTEXT.

Prepare sua tabela para usar o FULLTEXT

Depois de povoar a tabela com os dados importados do arquivo texto, prepare-a para usar a busca por FULLTEXT adicionando esta diretiva ao index. Isto pode ser feito no ato da criação da tabela ou, depois, com a declaração ALTER TABLE:

ALTER TABLE biblia ADD FULLTEXT (textover);
Query OK, 31110 rows affected (0.98 sec)
Records: 31110  Duplicates: 0  Warnings: 0

Embora seja possível incluir a definição index na declaração CREATE TABLE, este método (ALTER TABLE…) é mais eficiente do que povoar a tabela, previamente indexada, com um grande volume de dados.

Como fazer buscas no MySQL, usando o index

Para realizar suas pesquisas dentro de uma tabela, através do index, use a função MATCH() para nomear a coluna indexada e a função AGAINST() para especificar qual texto deve ser encontrado.
Por exemplo, se quiser saber quantas vezes o nome Elias é citado (você já teve esta curiosidade?), faça a busca dentro da coluna textover, com a seguinte declaração:

SELECT COUNT(*) from biblia WHERE MATCH(textover) AGAINST('Elias');
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
1 row in set (0.01 sec)

Faça suas próprias tentativas, para fixar o conhecimento, antes de seguir em frente.
Se quiser ver alguns destes versículos, expostos verticalmente, para facilitar a leitura, use uma declaração como a que segue:

SELECT numcap, numver, textover FROM biblia 
    -> WHERE MATCH(textover) AGAINST('Elias') LIMIT 5\G;
*************************** 1. row ***************************
  numcap: 17
  numver: 23
textover: E Elias tomou o menino, trouxe-o do quarto à casa, e o entregou a sua mãe e disse Elias: Vês aí, teu filho vive:
*************************** 2. row ***************************
  numcap: 2
  numver: 1
textover: Quando o Senhor estava para tomar Elias ao céu num redemoinho, Elias partiu de Gilgal com Eliseu.
*************************** 3. row ***************************
  numcap: 19
  numver: 13
textover: E ao ouvi-la, Elias cobriu o rosto com a capa e, saindo, pôs-se à entrada da caverna. E eis que lhe veio uma voz, que dizia: Que fazes aqui, Elias?
*************************** 4. row ***************************
  numcap: 18
  numver: 40
textover: Disse-lhes Elias: Agarrai os profetas de Baal! que nenhum deles escape: Agarraram-nos; e Elias os fez descer ao ribeiro de Quisom, onde os matou.
*************************** 5. row ***************************
  numcap: 18
  numver: 7
textover: Quando, pois, Obadias já estava em caminho, eis que Elias se encontrou com ele; e Obadias, reconhecendo-o, prostrou-se com o rosto em terra e disse: És tu, meu senhor Elias?
5 rows in set (0.00 sec)

Ainda que os resultados estejam ordenados “corretamente” por capítulo, versos na sua query (na minha, não estão, como você pode perceber), será mera coincidência.
Por padrão, o resultado da buscia via FULLTEXT é ordenado conforme um ranking próprio de relevância.
Use a cláusula ORDER BY para ordenar seus resultados explícitamente:

SELECT numcap, numver, textover FROM biblia 
    -> WHERE MATCH(textover) AGAINST('Elias')
    -> ORDER BY numliv,numcap,numver LIMIT 5\G;

Veja como o resultado é diferente:

*************************** 1. row ***************************
  numcap: 17
  numver: 1
textover: Então Elias, o tisbita, que habitava em Gileade, disse a Acabe: Vive o Senhor, Deus de Israel, em cuja presença estou, que nestes anos não haverá orvalho nem chuva, senão segundo a minha palavra.
*************************** 2. row ***************************
  numcap: 17
  numver: 2
textover: Depois veio a Elias a palavra do Senhor, dizendo:
*************************** 3. row ***************************
  numcap: 17
  numver: 13
textover: Ao que lhe disse Elias: Não temas; vai, faze como disseste; porém, faze disso primeiro para mim um bolo pequeno, e traz-mo aqui; depois o farás para ti e para teu filho.
*************************** 4. row ***************************
  numcap: 17
  numver: 15
textover: Ela foi e fez conforme a palavra de Elias; e assim comeram, ele, e ela e a sua casa, durante muitos dias.
*************************** 5. row ***************************
  numcap: 17
  numver: 16
textover: Da vasilha a farinha não se acabou, e da botija o azeite não faltou, conforme a palavra do Senhor, que ele falara por intermédio de Elias.
5 rows in set (0.08 sec)

Como adicionar índices às outras colunas

Se houver texto relevante nas outras colunas, pode ser interessante aplicar o index a elas também. Isto poderia ser feito assim:

ALTER TABLE biblia ADD INDEX (numliv), ADD INDEX (numcap), ADD INDEX (numver);

No “exemplo bíblico”, usado aqui, isto não teria muita utilidade. Mas em outras tabelas pode ajudar a tornar as buscas mais eficientes.

Adicione mais palavras à sua busca FULLTEXT

Se já sabemos que Elias é citado 100 vezes nesta versão da bíblia, vamos descobrir quantas vezes Elias e Eliseu (o profeta que o substituiu) são citados:

SELECT COUNT(*) from biblia WHERE MATCH(textover) AGAINST('Elias Eliseu');
+----------+
| COUNT(*) |
+----------+
|      156 |
+----------+
1 row in set (0.00 sec)

E se acrescentarmos as citações a Obadias a esta conta?

SELECT COUNT(*) from biblia WHERE MATCH(textover) AGAINST('Elias Eliseu Obadias');
+----------+
| COUNT(*) |
+----------+
|      172 |
+----------+

1 row in set (0.04 sec)[/false]
Se você necessitar fazer pesquisas dentro de múltiplas colunas, simultâneamente, nomeie-as todas quando for constituir o seu index?

ALTER TABLE tabela ADD FULLTEXT (col1, col2, col3);

Conclusão

Daqui pra frente, você pode fazer sua própria busca pelos seus personagens favoritos da bíblia.
A tabela biblia pode ser usada para aprender mais sobre o MySQL — conheça as minhas receitas MySQL.

By Elias Praciano

Autor de tecnologia (livre, de preferência), apaixonado por programação e astronomia.
Fã de séries, como "Rick and Morty" e "BoJack Horseman".
Me siga no Twitter e vamos trocar ideias!

4 replies on “Introdução a buscas via FULLTEXT no MySQL”

Bom dia Elias Praciano. Muito obrigada por compartilhar conhecimento. Faz muita gente feliz acredite!!

Por favor, tenho uma string (campo Aviso) que preciso desmembrar em várias strings sendo que cada registro do resultado deverá ter o campo nr_ordem em uma coluna, a data em outra coluna, a hora em outra e o comentário em outra. Porém o campo comentário pode ser de qq tamanho. Não achei nada que conseguisse resolver.

Segue um exemplo:

Campo Nr_Ordem: 1234567

Campo Aviso: 26/01/2021 13:51:29 – FXXXXXXX – Efetuado Solicitação de Pagamento 26/01/2021 14:28:36 – FXXXXXXX – Efetuado Liberação da Ordem com parecer da dependência autorizando o processamento 27/01/2021 12:08:26 FXXXXXXX – Efetuado Devolução da Solicitação 27/01/2021 12:42:27 – FXXXXXXX – Efetuado Regularização de Ordem 28/01/2021 12:46:03 FXXXXXXX – Efetuado Liquidação da Ordem

Poderia me ajudar por gentileza?

Deus abençoe e ilumine sempre seu aprendizado.

Gratidão.

Desculpe, postei a query com alguns parâmetros errados.
Segue a query correta:

SELECT * FROM tabela WHERE campo_int = ‘1’ AND (campo_texto LIKE ‘%palavra1%’ AND campo_texto LIKE ‘%palavra2%’ AND campo_texto LIKE ‘%palavra3%’ AND campo_texto LIKE ‘%palavra4%’);

Ou seja, o objetivo desta query é localizar campos que possuam em seu conteúdo TODAS as palavras (palavra1 a palavra4), independente da ordem em que se encontrem no texto.

Olá Elias Praciano!
Muito bacana o seu post.
Tendo buscado informações sobre o FULLTEXT, mas ainda não localizei uma solução definitiva para a query abaixo:
SELECT * FROM tabela WHERE campo_int = ‘1’ AND (campo_texto LIKE ‘%palavra1%’ OR campo_texto LIKE ‘%palavra2%’ OR campo_texto LIKE ‘%palavra3%’ OR campo_texto LIKE ‘%palavra4%’);

Tem alguma sugestão?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.