Introdução a buscas via FULLTEXT no MySQL

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.

Casamento de padrões com expressões regulares no MySQL

Saiba como usar o operador REGEXP e expressões regulares para efetuar um casamento de padrões, em vez de uma comparação literal.
O uso de padrões SQL são implementados por outros sistemas de bancos de dados — o que os torna facilmente portáveis além do MySQL.
Por outro lado, são um tanto limitados.
Um exemplo desta limitação é que é fácil criar um padrão SQL %abc% para encontrar um string que contenha “abc” — mas é muito difícil criar um padrão que identifique strings contendo um destes caracteres “a”, “b” ou “c”.
Para este tipo de operação (e muitas outras), o MySQL provê suporte a um outro tipo de casamento de padrões, baseado em expressões regulares (ou regular expressions) e o operador REGEXP (ou NOT REGEXP).
As operações pelo REGEXP usam elementos diferentes dos operadores ‘%’ e ‘_’ (veja o link no final) — que não têm muita importância aqui.

O MySQL usa a implementação de Henry Spencer, que está de acordo com o POSIX 1003.2. O banco de dados usa a versão estendida para dar suporte a operações de casamento de padrões executadas pelo operador REGEXP nas declarações SQL.

Opcionalmente, é possível usar o operador RLIKE, que é sinônimo de REGEXP — presente para manter compatibilidade com o mSQL (miniSQL), o que facilita a portabilidade entre os dois sistemas.
Conheça os padrões na tabela abaixo:

Padrão O que ele reconhece
^ O início de uma string.
$ O fim de uma string.
. Todo e qualquer caractere.
[…] Todo caractere incluido entre os colchetes.
[^…] Qualquer caractere que não esteja incluído entre os colchetes.
p1|p2|p3 Operador de alternância; encontra quaisquer padrões que sejam p1, p2 ou p3.
* Nenhuma ou mais das instâncias do elemento precedente.
+ Uma ou mais das instâncias do elemento precedente.
{n} n instâncias do elemento precedente
{m,n} de m a n instâncias do elemento precedente.

Esta tabela será melhor entendida a partir do momento em que forem dados alguns exemplos de uso dos operadores. Por enquanto, guarde-a para consulta.
Usuários mais avançados do Unix ou GNU/Linux podem estar familiarizados com o uso de caracteres de padrão de expressões regulares. Programadores Perl, PHP e Python, provavelmente também.
No artigo Como encontrar padrões entre os seus dados no MySQL mostrei como encontrar substrings no começo ou no fim de uma cadeia de caracteres (string) ou em uma posição aribtrária ou específica dentro de uma string.
O mesmo pode ser feito com expressões regulares.

Exemplos de uso de expressões regulares, para encontrar informações no MySQL

Daqui pra frente, vamos ver como fazer uso dos operadores, expostos na tabela.
O exemplo, abaixo, mostra como encontrar todos nomes que terminam com “an”:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente REGEXP 'an$';
+------------------+
| NomeCliente      |
+------------------+
| Aiko Tran        |
| Karleigh Norman  |
| Lillith Sloan    |
| Christen Kaufman |
| Camilla Freeman  |
+------------------+
5 rows in set (0.00 sec)

Para encontrar os nomes de clientes, na minha tabela, que começam com “Li”…

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente REGEXP '^li';
+---------------+
| NomeCliente   |
+---------------+
| Lillith Sloan |
+---------------+
1 row in set (0.00 sec)

Percebeu a insensibilidade do REGEXP à caixa das letras?
Se eu quiser listar as strings que contém a sequência “man”, em qualquer posição:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente REGEXP 'man';
+------------------+
| NomeCliente      |
+------------------+
| Irene Mann       |
| Karleigh Norman  |
| Christen Kaufman |
| Camilla Freeman  |
+------------------+
4 rows in set (0.00 sec)

No exemplo que segue, mostro como encontrar as strings que contém o caractere “i” a partir da 3a. posição na sequência:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente REGEXP '^...i';
+------------------+
| NomeCliente      |
+------------------+
| Christine Mccall |
| April Hubbard    |
| Regina Swanson   |
| Christen Kaufman |
| Madison Love     |
| Camilla Freeman  |
| Cecilia Everett  |
| Kylie Bradshaw   |
| Zenia Carr       |
| Jolie Riggs      |
| Larissa Rich     |
| Vivian Richmond  |
| Indira Parker    |
| Calista Barrett  |
+------------------+
14 rows in set (0.00 sec)

A brincadeira com as expressões regulares poderia se estender por dias a fio. O assunto é extenso e será, com certeza, abordado em outros posts — e não somente quando estiver relacionado ao MySQL.

Referências

Como casar padrões no MySQL usando os operadores ‘%’ e ‘_’: https://elias.praciano.com/2015/04/como-encontrar-padroes-entre-os-seus-dados-no-mysql/
MySQL Regular Expressions with The REGEXP Operator: http://www.regular-expressions.info/mysql.html
Oracle documentation: https://dev.mysql.com/doc/refman/5.7/en/regexp.html

Como encontrar padrões entre os seus dados no MySQL

Esta solução se aplica à busca de padrões em meio ao banco de dados MySQL, com o uso do operador LIKE.
Padrões são strings contendo caracteres especiais.
MySQL_Positive_Energy_001
São conhecidos como meta caracteres porque representam algo diferente de si mesmos.

“Casamento de padrões é o ato de verificação da presença de um padrão em um conjunto de dados (…) usado para testar se o objeto de estudo possui a estrutura desejada (…)” Wikipedia.

O MySQL tem 2 tipos de casamento de padrões. Um, baseado em padrões SQL e outro baseado em expressões regulares.
Cada um destes usa seus próprios operadores e diferentes sets de meta caracteres.
Neste texto, vou me limitar aos padrões SQL.

Exemplos de uso do operador LIKE no MySQL

O casamento de padrões SQL usa os operadores LIKE e NOT LIKE – em vez de = ou != – para encontrar paridade entre padrões.
Os padrões podem conter dois meta caracteres especiais (ou coringas):

  • % — o sinal de percentual serve para buscar a correspondência exata de caracteres — sem se preocupar com o posicionamento deles dentro da string.
  • _ — o sinal de sublinha ou underscore procura a correspondência, só que dentro do mesmo posicionamento de caracteres.
  • São caracteres-curinga, portanto. Semelhantes aos que você usa em um terminal para se referir a vários nomes de arquivos que sejam parcialmente iguais. Ex.: mysql.*, config*.ini etc.

    A palavra curinga se origina do termo “kuringa”, em kimbundo. E significa “matar”.
    via Wikipedia.

Complicado? Então, vamos deixar os exemplos falarem por si.
Veja como fazer uma busca, dentro da tabela ClientesCidades, por todos os nomes de cidades que contenham a sequência de caracteres “co”, no início:

SELECT CidadeCliente FROM ClientesCidades WHERE CidadeCliente LIKE 'co%';
+-----------------+
| CidadeCliente   |
+-----------------+
| Corroy-le-Grand |
| Coalhurst       |
| Colonnella      |
+-----------------+
3 rows in set (0.04 sec)

Você não precisa usar as minhas tabelas para aplicar os exemplos no seu aprendizado. Pode usar qualquer outra, que você tiver disponível. Basta readequar os exemplos deste texto à sua realidade.
Só não vá brincar com os dados da produção.


Se quiser encontrar os nomes de cidade que terminem com a sequência “co”, inverta a posição do sinal de percentual (%):

SELECT CidadeCliente FROM ClientesCidades WHERE CidadeCliente LIKE '%co';
+---------------+
| CidadeCliente |
+---------------+
| Girifalco     |
| Recco         |
+---------------+

Ou, encontre todos nomes de cidade que contenham a sequência “co”, em qualquer posição na string:

SELECT CidadeCliente FROM ClientesCidades WHERE CidadeCliente LIKE '%co%';
+------------------------+
| CidadeCliente          |
+------------------------+
| Girifalco              |
| San Costantino Calabro |
| Rocourt                |
| Beaconsfield           |
| Rachecourt             |
| Vancouver              |
| Lacombe                |
| Port Lincoln           |
| Corroy-le-Grand        |
| Recco                  |
| Coalhurst              |
| San Demetrio Corone    |
| Lakeland County        |
| Beaconsfield           |
| Colonnella             |
+------------------------+
15 rows in set (0.01 sec)

Note que, neste caso, o operador sequer exige que haja a correspondência da caixa das letras — ou seja, “Colonnella” e “u>colonnella” são a mesma coisa.
Ao criar um sistema de buscas, usar coringas é uma opção para encontrar e exibir uma série de resultados relacionados.
O caractere-curinga underscore (_) é mais restritivo. Ele só serve para substituir os caracteres que se encontram na posição em que ele for colocado na busca.

Uso de curinga Correspondências possívels
__enda Brenda, Glenda
___nda Brenda, Glenda, Amanda, Chanda.
C_ntia Cíntia, Cintia, Cyntia
C_n%ia Cíntia, Cintia, Cyntia, Cynthia, Cínthia etc.

Espero que a tabela ajuda a entender melhor o funcionamento do underscore no SQL.
Veja alguns exemplos de uso do LIKE, combinado a este caractere-curinga:

SELECT Nome FROM Clientela WHERE Nome LIKE 'Al__a';
+----------+
| Nome     |
+----------+
| Alisa    |
| Alana    |
| Alexa    |
| Alana    |
+----------+
4 rows in set (0.00 sec)

Combine o uso dos caracteres-curinga ‘%’ e ‘_’ para enriquecer seus resultados — o que pode ser necessário, se a coluna incluir nome e sobrenome:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente LIKE 'I__ana%' ORDER BY NomeCliente;
+------------------+
| NomeCliente      |
+------------------+
| Iliana Cleveland |
| Illana Cannon    |
+------------------+
2 rows in set (0.00 sec)

Como usar o NOT LIKE em uma query MySQL

Se eu quiser uma relação de todos os nomes que não contenham uma determinada letra ou sequência de caracteres, posso usar o NOT LIKE para fazer o trabalho. Veja um exemplo, que exclui todos os nomes (e sobrenomes) que contenham a letra ‘a’:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente NOT LIKE '%a%' ORDER BY NomeCliente;
+--------------------+
| NomeCliente        |
+--------------------+
| Bo Simpson         |
| Emily Holmes       |
| Emily Tyson        |
| Giselle Rice       |
| Helen Burke        |
| Melodie Levy       |
| Mercedes Hendricks |
| Michelle Whitley   |
| Simone Everett     |
| Yvonne Wilcox      |
+--------------------+
10 rows in set (0.00 sec)

Como combinar as buscas com LIKE e outras funções de manipulação de strings no MySQL

Use funções específicas para retirar o que te interessa nas strings:

SELECT NomeCliente FROM ClientesCidades WHERE LEFT(Nomecliente,3) LIKE 'wil%' ORDER BY NomeCliente;
+-----------------+
| NomeCliente     |
+-----------------+
| Willa Kerr      |
| Willow Graham   |
| Willow Mitchell |
| Willow Simmons  |
| Wilma Hill      |
+-----------------+
5 rows in set (0.00 sec)

Leia mais sobre funções para decompor strings ou use o quadro de busca desta página para se aprofundar mais no assunto.

Referências: Casamentos de padrão na Wikipedia: http://pt.wikipedia.org/wiki/Casamento_de_padr%C3%B5es.
Como pesquisar sequências de caracteres dentro de strings, com a função LOCATE, no MySQL.