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.

Como seccionar uma lista, usando a cláusula LIMIT do MySQL

A cláusula LIMIT pode ser usada para obter informações contidas no meio de uma lista, e não apenas nas extremidades (início ou fim) da consulta.
Capa do tutorial MySQL - a cláusula LIMIT
O uso básico da cláusula LIMIT, já foi discutido no artigo Como usar LIMIT para selecionar registros do começo ou do fim de uma tabela no MySQL — sugiro-o como leitura complementar.
Neste texto, vou mostrar como obter determinadas linhas, no “miolo” de uma relação e como é possível fazer paginação, com LIMIT — ou seja, dividir uma relação em múltiplas páginas.
Para que o LIMIT possa realizar a tarefa é necessário que você diga em que posição, na lista, ele deve começar, além da quantidade de linhas que você deseja que sejam exibidas.
A cláusula LIMIT aceita 2 argumentos:

  • O primeiro determina quantas linhas devem ser “puladas” ou ignoradas.
  • O segundo determina quantas linhas da lista devem ser exibidas.

Isto significa que você pode usar a cláusula LIMIT para pular 2 linhas e mostram a próxima, por exemplo. Isto permite responder perguntas um pouco mais complexas, como “Qual o quinto maior valor num determinado campo?” ou “Qual o terceiro menor valor?” — que seria mais difíceis de responder com as funções MIN() ou MAX().
Vamos aos exemplos.

Como paginar resultados com a cláusula LIMIT, no MySQL

Distribuir em várias páginas os resultados de uma query MySQL com o LIMIT é muito fácil — ele é feito pra isso.
Veja um exemplo básico de paginação de 2 em 2 (para não ficar muito extenso):

SELECT idCli,nomeCli,emailCli FROM CadCli LIMIT 2;

Na primeira parte, peço a lista limitada aos 2 primeiros registros:

+-------+-----------------+--------------------------------+
| idCli | nomeCli         | emailCli                       |
+-------+-----------------+--------------------------------+
|     1 | Tana Mclaughlin | eget.tincidunt@magnisdis.net   |
|     2 | Jakeem Klein    | mauris.eu@Nuncmaurissapien.com |
+-------+-----------------+--------------------------------+
2 rows in set (0.00 sec)

A lista segue, pulando 2 resultados, limitada a 2 linhas:

SELECT idCli,nomeCli,emailCli FROM CadCli LIMIT 2,2;
+-------+-----------------+-------------------------------+
| idCli | nomeCli         | emailCli                      |
+-------+-----------------+-------------------------------+
|     3 | Wylie Carroll   | convallis.in.cursus@libero.ca |
|     4 | Colton Franklin | dui.lectus.rutrum@eusem.org   |
+-------+-----------------+-------------------------------+
2 rows in set (0.00 sec)

Concluo o exemplo, exibindo a próxima página, depois do quarto registro, limitado a 2 linhas:

 SELECT idCli,nomeCli,emailCli FROM CadCli LIMIT 4,2;
+-------+-----------------+------------------------------------------+
| idCli | nomeCli         | emailCli                                 |
+-------+-----------------+------------------------------------------+
|     5 | Amity Whitfield | risus.Quisque.libero@Praesenteudui.co.uk |
|     6 | Sade Carver     | Donec@Innec.com                          |
+-------+-----------------+------------------------------------------+
2 rows in set (0.00 sec)

Dentro de um script, podemos usar um loop para fazer este trabalho.


Nota: Você pode usar qualquer tabela disponível para você para praticar. Se você fizer questão de usar a mesma tabela deste artigo, é possível obtê-la aqui.

Como obter o maior ou menor valor de um campo, usando o LIMIT

Para realizar este trabalho, vou acrescentar a cláusula DESC em uma das queries.
Veja como obter o maior valor em uma coluna da tabela:

SELECT nomeCli, creditoCli FROM CadCli ORDER BY creditoCli DESC LIMIT 1;
+---------------+------------+
| nomeCli       | creditoCli |
+---------------+------------+
| Steven Stokes |      84831 |
+---------------+------------+
1 row in set (0.00 sec)

Se a pergunta fosse “Qual o email do cliente com o menor valor de crédito?”, a resposta poderia ser obtida assim:

SELECT nomeCli AS Cliente, emailCli AS "Email p/contato", creditoCli FROM CadCli ORDER BY creditoCli LIMIT 1;
+-----------------+----------------------------------+------------+
| Cliente         | Email p/contato                  | creditoCli |
+-----------------+----------------------------------+------------+
| Hanna Mccormick | molestie.tortor.nibh@indolor.org |        600 |
+-----------------+----------------------------------+------------+
1 row in set (0.00 sec)

E, se a pergunta for “Qual o nome do cliente com o segundo maior crédito?”

SELECT nomeCli AS Cliente, emailCli AS "Email p/contato", creditoCli FROM CadCli ORDER BY creditoCli DESC LIMIT 1,1;
+-------------+-----------------------+------------+
| Cliente     | Email p/contato       | creditoCli |
+-------------+-----------------------+------------+
| Yvonne Wise | habitant@odioEtiam.ca |      84531 |
+-------------+-----------------------+------------+
1 row in set (0.00 sec)

Espero que este post resolva as suas dúvidas sobre como seccionar relatórios no MySQL, como a cláusula LIMIT. Você pode usar a caixa de buscas, no topo do site, para encontrar tutoriais e receitas MySQL.

Como usar LIMIT para selecionar registros do começo ou do fim de uma tabela no MySQL

A cláusula LIMIT, usada em conjunto com a cláusula ORDER BY, permite limitar a exibição dos registros de uma tabela a um determinado número.
O recurso é muito útil para mim, que estou sempre mostrando exemplos de uso de vários outros recursos do MySQL — mas não posso encher esta página com informações inúteis para os leitores.
Capa do tutorial MySQL LIMIT
O leitor quer ver apenas como um determinado recurso funciona… não a tabela toda!
Além disto, o recurso é útil quando se deseja mostrar apenas as últimas alterações ocorridas em uma tabela de um banco de dados, como seria o caso de um sistema de logging.
Uma outra situação em que este recurso é usado: quando o desenvolvedor deseja paginar os resultados — ou seja, distribuir uma relação por várias páginas. Uma de cada vez. Um exemplo disto, são as páginas do resultado de busca do Google.

Como funciona a cláusula LIMIT no MySQL

O MySQL suporta o uso desta cláusula, que serve para informar ao servidor que ele só deve retornar uma parte do resultado pedido em uma consulta.
Os exemplos, que seguem, fazem uso da tabela CadCli, que pode ser encontrada aqui — mas você pode usar qualquer outra. Basta adequar os comandos à sua situação.
Para limitar a exibição dos resultados de uma query a 1 registro, use a cláusula LIMIT 1:

SELECT * FROM CadCli LIMIT 1;
+-------+-----------------+------------------------------+-----------+------------+----------+----------+
| idCli | nomeCli         | emailCli                     | cidadeCli | creditoCli | dtCadCli | dtNscCli |
+-------+-----------------+------------------------------+-----------+------------+----------+----------+
|     1 | Tana Mclaughlin | eget.tincidunt@magnisdis.net | Stekene   |      27768 | 07-06-14 | 11-02-09 |
+-------+-----------------+------------------------------+-----------+------------+----------+----------+
1 row in set (0.00 sec)

Aumente o valor de LIMIT, para obter mais resultados:

SELECT * FROM CadCli LIMIT 5;
+-------+-----------------+------------------------------------------+---------------+------------+----------+----------+
| idCli | nomeCli         | emailCli                                 | cidadeCli     | creditoCli | dtCadCli | dtNscCli |
+-------+-----------------+------------------------------------------+---------------+------------+----------+----------+
|     1 | Tana Mclaughlin | eget.tincidunt@magnisdis.net             | Stekene       |      27768 | 07-06-14 | 11-02-09 |
|     2 | Jakeem Klein    | mauris.eu@Nuncmaurissapien.com           | Lagos         |      17465 | 02-07-14 | 29-04-71 |
|     3 | Wylie Carroll   | convallis.in.cursus@libero.ca            | Lelystad      |      81512 | 19-09-14 | 13-08-98 |
|     4 | Colton Franklin | dui.lectus.rutrum@eusem.org              | Sesto Campano |      68180 | 13-04-15 | 27-07-77 |
|     5 | Amity Whitfield | risus.Quisque.libero@Praesenteudui.co.uk | Fauvillers    |      21975 | 10-07-14 | 01-02-82 |
+-------+-----------------+------------------------------------------+---------------+------------+----------+----------+
5 rows in set (0.00 sec)

Melhore o resultado filtrando
O valor de LIMIT não pode ser negativo.
Se você quiser, de alguma forma, inverter a ordem de exibição dos registros, use as cláusulas WHERE e ORDER BY para filtrar os seus registros:

SELECT idCli AS "Ordem",nomeCli AS "Cliente", creditoCli AS "Credito Disponível" FROM CadCli ORDER BY CreditoCli DESC LIMIT 5;

O comando, acima, pede a relação dos clientes ordenada pelo crédito disponível (creditoCli), em ordem descendente e limitado a 5 registros. O meu resultado foi o seguinte:

+-------+----------------+---------------------+
| Ordem | Cliente        | Credito Disponível  |
+-------+----------------+---------------------+
|    78 | Steven Stokes  |               84831 |
|    54 | Yvonne Wise    |               84531 |
|     8 | Ocean Greene   |               84520 |
|   100 | Kendall Morton |               83838 |
|    19 | Lydia Patton   |               83368 |
+-------+----------------+---------------------+
5 rows in set (0.00 sec)

Você deve usar sempre as cláusulas WHERE e ORDER BY, combinadas ao LIMIT, para obter resultados mais significativos para as suas necessidades.
É possível obter os mesmos resultados, acima, sem usar a cláusula LIMIT.
A vantagem de fazer uso do LIMIT é que o servidor vai retornar a informação pedida — o que estiver fora dos limites definidos em LIMIT não será enviado. Desta forma, o uso da rede e da sua largura de banda é muito mais eficiente com o uso do LIMIT.
Em um universo de um milhão de registros, se eu quisesse ver apenas quem tem o maior valor em créditos disponíveis, nesta tabela, eu posso usar o LIMIT, sem sobrecarregar a rede com tráfego desnecessário de dados:

SELECT * FROM CadCli ORDER BY CreditoCli DESC LIMIT 1;
+-------+---------------+-----------------------------+------------+------------+----------+----------+
| idCli | nomeCli       | emailCli                    | cidadeCli  | creditoCli | dtCadCli | dtNscCli |
+-------+---------------+-----------------------------+------------+------------+----------+----------+
|    78 | Steven Stokes | est.Nunc.laoreet@cursus.edu | Llandovery |      84831 | 17-12-14 | 18-07-75 |
+-------+---------------+-----------------------------+------------+------------+----------+----------+
1 row in set (0.00 sec)

Use o quadro de pesquisa, no canto superior da página para encontrar mais artigos, neste site, sobre o uso do LIMIT e/ou outras cláusulas MySQL.

Como ordenar resultados das queries MySQL

Você pode ordenar suas consultas ao banco de dados MySQL, feitas com o comando SELECT, através de uma cláusula ORDER BY (que quer dizer ordene por, em uma tradução livre para português).
Neste texto, vou mostrar como ordenar queries MySQL pode ser fácil e flexibilizado.
MySQL logo Dolphin in gray shades
Os exemplos dados neste tutorial são genéricos e podem ser aplicados, com as devidas alterações, a qualquer tabela que você tiver disponível para seu aprendizado. Mas, se quiser, pode pegar a tabela que estou usando aqui.

Filtre seus resultados com WHERE, ordene com BY ORDER

Se você usar o quadro de busca, no topo do site, vai encontrar vários exemplos de uso da cláusula WHERE — que serve para filtrar os resultados obtidos pelo SELECT.
No exemplo abaixo, selecionamos para exibição os nomes de clientes e valor de seus créditos, onde o valor do crédito for maior que 82000, ordenado por valor do crédito ASCendentemente:

SELECT nomeCli, creditoCli FROM CadCli WHERE creditoCli > 82000 ORDER BY creditoCli ASC;
+----------------+------------+
| nomeCli        | creditoCli |
+----------------+------------+
| Daphne Blair   |      82413 |
| Lydia Patton   |      83368 |
| Kendall Morton |      83838 |
| Ocean Greene   |      84520 |
| Yvonne Wise    |      84531 |
| Steven Stokes  |      84831 |
+----------------+------------+
6 rows in set (0.01 sec

O comportamento padrão do MySQL é ordenar ascendentemente — ou seja, se você quiser, não precisa usar a cláusula ASC, nesta consulta.
Caso queira aplicar uma ordem descendente, por nomes, use o exemplo que segue:

SELECT nomeCli, creditoCli FROM CadCli WHERE creditoCli > 82000 ORDER BY nomeCli DESC;
+----------------+------------+
| nomeCli        | creditoCli |
+----------------+------------+
| Yvonne Wise    |      84531 |
| Steven Stokes  |      84831 |
| Ocean Greene   |      84520 |
| Lydia Patton   |      83368 |
| Kendall Morton |      83838 |
| Daphne Blair   |      82413 |
+----------------+------------+
6 rows in set (0.00 sec)

Você pode usar mais do que um parâmetro de ordenação. No exemplo que segue, vou ordenar o resultado por nome do cliente e por nome da cidade. Veja:

SELECT nomeCli, cidadeCli, creditoCli from CadCli WHERE creditoCli < 8000 ORDER BY nomeCli, cidadeCli;

O resultado sairá ordenado primeiro por nomeCli e, secundariamente, por cidadeCli. Como não foi especificado, a ordem da lista, por padrão é ascendente:

+-----------------+----------------------+------------+
| nomeCli         | cidadeCli            | creditoCli |
+-----------------+----------------------+------------+
| Deacon Reid     | Cuglieri             |       7937 |
| Fuller Richards | Airdrie              |       7252 |
| Hanna Mccormick | Lens-Saint-Remy      |        600 |
| Lois Dyer       | Torrevecchia Teatina |       6057 |
| Sheila Foreman  | Sloten               |       4266 |
| Warren Burgess  | Konstanz             |       1656 |
+-----------------+----------------------+------------+
6 rows in set (0.00 sec)

Encontre outros exemplos interessantes, usando a caixa de busca no topo do site ou clicando na tag correspondente, logo abaixo.