Capacidades de Busca Full-Text do MySQL

Introdução

Neste laboratório, você explorará as capacidades de busca full-text do MySQL. Você começará criando um banco de dados e uma tabela chamada articles com colunas para id, title e content, e então a preencherá com dados de exemplo.

Em seguida, você adicionará um índice full-text chamado article_index à tabela articles, especificamente nas colunas title e content, usando a instrução ALTER TABLE. Finalmente, você verificará a criação do índice usando o comando SHOW INDEXES, confirmando que o Index_type é FULLTEXT. Esta configuração o preparará para buscas de texto eficientes em seu banco de dados MySQL.

Ao longo deste laboratório, você trabalhará dentro do shell do MySQL. Você só precisa entrar no shell do MySQL no início e sair no final. Todos os comandos SQL nas etapas seguintes devem ser executados dentro da mesma sessão do MySQL.

Criar Banco de Dados e Tabela

Nesta etapa, você criará um banco de dados MySQL e uma tabela para armazenar artigos. Esta tabela será usada para demonstrar as capacidades de busca full-text.

Primeiro, abra seu terminal e conecte-se ao servidor MySQL como usuário root. Você pode fazer isso usando o seguinte comando:

sudo mysql -u root

Este comando conecta-se ao servidor MySQL usando o usuário root. Como você está usando sudo, não será solicitada uma senha.

Uma vez conectado ao shell do MySQL, você verá o prompt mysql>. Permaneça no shell do MySQL para todas as etapas subsequentes até o final do laboratório.

Agora, vamos criar um banco de dados chamado search_db. Este banco de dados conterá nossa tabela articles. Execute o seguinte comando SQL:

CREATE DATABASE IF NOT EXISTS search_db;

A cláusula IF NOT EXISTS garante que o banco de dados só seja criado se ele ainda não existir.

Em seguida, mude para o banco de dados recém-criado usando o comando USE:

USE search_db;

Você deverá ver uma mensagem indicando que o banco de dados foi alterado.

Agora, crie a tabela articles. Esta tabela terá três colunas: id, title e content.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

Vamos detalhar este comando:

• CREATE TABLE articles: Esta instrução cria uma nova tabela chamada articles.
• id INT AUTO_INCREMENT PRIMARY KEY: Isso define uma coluna de inteiro chamada id que incrementa automaticamente para cada nova linha e serve como chave primária para a tabela.
• title VARCHAR(255) NOT NULL: Isso define uma coluna de string chamada title com um comprimento máximo de 255 caracteres. NOT NULL significa que esta coluna não pode estar vazia.
• content TEXT: Isso define uma coluna de texto chamada content que pode armazenar strings maiores.

Após executar este comando, a tabela articles será criada dentro do banco de dados search_db.

Você pode verificar a criação da tabela listando as tabelas no banco de dados atual:

SHOW TABLES;

Você deverá ver articles listado na saída.

+-----------------------+
| Tables_in_search_db   |
+-----------------------+
| articles              |
+-----------------------+
1 row in set (0.00 sec)

Agora que você criou o banco de dados e a tabela, está pronto para preenchê-lo com alguns dados de exemplo na próxima etapa.

Inserir Dados de Exemplo e Adicionar Índice Full-Text

Nesta etapa, você inserirá dados de exemplo na tabela articles e, em seguida, adicionará um índice full-text para permitir buscas de texto eficientes.

Certifique-se de que você ainda está no shell do MySQL e usando o banco de dados search_db. Se não estiver, selecione o banco de dados:

USE search_db;

Agora, vamos inserir alguns dados de exemplo na tabela articles. Adicionaremos três linhas com títulos e conteúdos diferentes:

INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'This article explains how to use full-text search in MySQL.'),
('Indexing in MySQL', 'Learn about different types of indexes in MySQL, including full-text indexes.'),
('Optimizing MySQL Queries', 'Tips and tricks for optimizing your MySQL queries for better performance.');

Esta instrução INSERT adiciona várias linhas à tabela articles. Cada conjunto de parênteses após VALUES representa uma nova linha, com os valores correspondendo às colunas title e content.

Você pode verificar se os dados foram inseridos selecionando todas as linhas da tabela:

SELECT * FROM articles;

Você deverá ver as três linhas inseridas na saída:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

Agora que a tabela contém dados, vamos adicionar um índice full-text. Um índice full-text permite que o MySQL execute buscas rápidas e relevantes em dados de texto. Adicionaremos um índice full-text nas colunas title e content.

Use a instrução ALTER TABLE para adicionar o índice:

ALTER TABLE articles ADD FULLTEXT INDEX article_index (title, content);

Vamos detalhar este comando:

• ALTER TABLE articles: Isso indica que estamos modificando a tabela articles.
• ADD FULLTEXT INDEX article_index: Isso adiciona um novo índice do tipo FULLTEXT e o nomeia article_index.
• (title, content): Isso especifica as colunas que serão incluídas no índice full-text.

Após executar este comando, o MySQL construirá o índice full-text nas colunas especificadas.

Para verificar se o índice full-text foi criado com sucesso, você pode usar o comando SHOW INDEXES:

SHOW INDEXES FROM articles;

A saída mostrará todos os índices na tabela articles. Você deverá ver uma entrada para article_index com FULLTEXT na coluna Index_type.

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| articles |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| articles |          1 | article_index  |            1 | title       | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
| articles |          1 | article_index  |            2 | content     | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Você agora inseriu com sucesso os dados e adicionou um índice full-text à sua tabela. Na próxima etapa, você aprenderá como usar este índice para realizar buscas full-text.

Realizar Pesquisa Full-Text Básica

Nesta etapa, você realizará buscas full-text básicas na tabela articles usando a cláusula MATCH AGAINST. Esta é a forma fundamental de utilizar o índice full-text que você criou.

Certifique-se de que você ainda está no shell do MySQL e usando o banco de dados search_db. Se não estiver, selecione o banco de dados:

USE search_db;

A cláusula MATCH AGAINST é usada dentro da cláusula WHERE de uma instrução SELECT. A sintaxe básica é:

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term');

Aqui, column_list é uma lista separada por vírgulas das colunas incluídas no índice full-text (no nosso caso, title e content), e 'search_term' é a palavra ou frase que você deseja pesquisar.

Vamos procurar por artigos que contenham a palavra "MySQL":

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('MySQL');

Esta consulta seleciona as colunas id, title e content da tabela articles onde as colunas title ou content correspondem ao termo "MySQL".

Você deverá ver a seguinte saída:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

Todos os três artigos são retornados porque todos contêm a palavra "MySQL".

A busca full-text também fornece uma pontuação de relevância, indicando o quão bem cada linha corresponde ao termo de busca. Você pode incluir essa pontuação em sua instrução SELECT:

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles;

Esta consulta adiciona uma coluna chamada relevance que mostra a pontuação para cada linha.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

Você pode ordenar os resultados por relevância para ver as melhores correspondências primeiro:

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles ORDER BY relevance DESC;

Esta consulta ordena os resultados com base na pontuação de relevance em ordem decrescente.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

Nesta etapa, você aprendeu como realizar buscas full-text básicas usando MATCH AGAINST e como recuperar e ordenar resultados por relevância. Na próxima etapa, você explorará opções de busca mais avançadas usando o modo booleano.

Usar Modo Booleano para Pesquisa Avançada

Nesta etapa, você usará o modo booleano da cláusula MATCH AGAINST para realizar buscas full-text mais precisas e flexíveis. O modo booleano permite que você use operadores para controlar quais palavras devem ou não estar presentes nos resultados.

Certifique-se de que você ainda está no shell do MySQL e usando o banco de dados search_db. Se não estiver, selecione o banco de dados:

USE search_db;

Para usar o modo booleano, você adiciona a cláusula IN BOOLEAN MODE à instrução MATCH AGAINST. A sintaxe é:

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term' IN BOOLEAN MODE);

Aqui estão alguns operadores comuns usados no modo booleano:

• +: Exige que a palavra esteja presente no resultado.
• -: Exclui linhas que contêm a palavra.
• "": Busca por uma frase exata.
• *: Operador curinga (no final de uma palavra).

Vamos encontrar artigos que devem conter a palavra "MySQL" e não devem conter a palavra "optimizing".

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -optimizing' IN BOOLEAN MODE);

Esta consulta usa o operador + para exigir "MySQL" e o operador - para excluir "optimizing".

Você deverá ver a seguinte saída:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

O artigo sobre "Optimizing MySQL Queries" é excluído porque contém "optimizing".

Agora, vamos procurar pela frase exata "full-text search":

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('"full-text search"' IN BOOLEAN MODE);

Usar aspas duplas busca pelas palavras como uma frase contígua.

A saída será:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Apenas o primeiro artigo é retornado porque contém a frase exata "full-text search".

Vamos tentar usar o operador curinga *. Suponha que você queira encontrar artigos que contenham palavras que comecem com "index".

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('index*' IN BOOLEAN MODE);

O termo index* corresponderá a palavras como "index" e "indexing".

A saída será:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Nesta etapa, você aprendeu como usar o modo booleano com a cláusula MATCH AGAINST e vários operadores para realizar buscas full-text mais controladas e específicas. Na próxima etapa, você praticará a combinação dessas técnicas.

Combinar Técnicas de Pesquisa

Nesta etapa, você combinará a busca básica MATCH AGAINST com operadores de modo booleano para realizar consultas full-text mais complexas e refinadas.

Certifique-se de que você ainda está no shell do MySQL e usando o banco de dados search_db. Se não estiver, selecione o banco de dados:

USE search_db;

Vamos tentar encontrar artigos que contenham a palavra "MySQL" e, ou a palavra "indexing" ou "optimizing". Podemos usar parênteses para agrupar os termos opcionais no modo booleano.

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL +(indexing optimizing)' IN BOOLEAN MODE);

Esta consulta exige "MySQL" (+MySQL) e exige pelo menos um dos termos dentro dos parênteses (+(indexing optimizing)).

A saída será:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

O primeiro artigo é excluído porque contém "MySQL", mas nem "indexing" nem "optimizing".

Você também pode combinar o modo booleano com pontuação de relevância. Vamos encontrar artigos que contenham "MySQL" e priorizar aqueles que também mencionam "indexing" usando o operador >.

SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) ORDER BY relevance DESC;

Esta consulta exige "MySQL" e aumenta a pontuação de relevância para artigos que contêm "indexing". Os resultados são então ordenados pela relevância calculada.

A saída será:

+----+--------------------------+---------------------------------------------------------------------+-----------+
| id | title                    | content                                                             | relevance |
+----+--------------------------+---------------------------------------------------------------------+-----------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |  1.6931  |
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |  0.3068  |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |  0.3068  |
+----+--------------------------+---------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

Note que o artigo "Indexing in MySQL" é classificado mais alto devido ao operador >indexing.

Ao combinar a busca básica com operadores booleanos e pontuação de relevância, você pode criar consultas full-text poderosas e flexíveis, adaptadas às suas necessidades específicas.

Isto conclui o laboratório sobre as capacidades de busca full-text do MySQL. Você pode agora sair do shell do MySQL digitando:

exit

Você aprendeu como configurar uma tabela com um índice full-text, realizar buscas básicas e usar o modo booleano para consultas mais avançadas.

Resumo

Neste laboratório, você aprendeu sobre as capacidades de busca full-text do MySQL. Você começou criando um banco de dados chamado search_db e uma tabela articles para armazenar dados textuais. Em seguida, você popularizou a tabela com artigos de exemplo.

A etapa chave foi adicionar um índice full-text chamado article_index às colunas title e content da tabela articles usando a instrução ALTER TABLE. Este índice é essencial para buscas full-text eficientes. Você verificou a criação do índice usando SHOW INDEXES.

Finalmente, você explorou como realizar buscas full-text usando a cláusula MATCH AGAINST. Você aprendeu como realizar buscas básicas, recuperar pontuações de relevância e ordenar os resultados por relevância. Você também se aprofundou no modo booleano, usando operadores como +, -, "" e * para criar consultas de busca mais precisas e complexas. Ao combinar essas técnicas, você pode efetivamente pesquisar e recuperar informações relevantes de dados de texto armazenados em seu banco de dados MySQL.