Índice de texto completo do MySQL – Acervo Lima Tutorials

Resumo : neste tutorial, você aprenderá sobre o índice de texto completo do MySQL e como usá-lo para realizar pesquisas de texto eficientes.

Introdução ao índice de texto completo do MySQL

O índice de texto completo do MySQL é um tipo de índice que permite realizar pesquisas de texto eficientes. O índice de texto completo tem o tipo FULLTEXT.

MySQL suporta índices de texto completo para tabelas InnoDB ou MyISAM com colunas CHAR , VARCHAR e TEXT .

Observe que o suporte para índice de texto completo no MySQL está disponível apenas para tabelas InnoDB a partir da versão 5.6.

O MySQL permite que você crie um FULLTEXTíndice na CREATE TABLEinstrução no momento da criação da tabela ou adicione-o à tabela posteriormente usando ALTER TABLEou CREATE FULLTEXT INDEX.

O MySQL fornece funções de pesquisa de texto completo, como MATCH()e AGAINST(), que você pode usar para pesquisar colunas incluídas nos índices de texto completo.

Criando um índice de texto completo do MySQL

O seguinte ilustra como criar um índice de texto completo para uma ou mais colunas de uma tabela:

CREATE TABLE tbl_name(
   column1 constaint, 
   column2 constaint, 
   ...,
   FULLTEXT(column1, column2)
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe, você coloca uma lista de nomes de colunas separados por vírgula entre parênteses após a FULLTEXTpalavra-chave.

Por exemplo, o seguinte cria uma nova tabela chamada postsque possui um índice de texto completo que inclui as colunas titlee :body

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT,

  FULLTEXT KEY (title, body)
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Adicionando um índice de texto completo a uma tabela existente usando a instrução ALTER TABLE

O MySQL permite adicionar um índice de texto completo a uma tabela existente usando a ALTER TABLEinstrução:

ALTER TABLE tbl_name
ADD FULLTEXT(column1, column2,…);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe:

Primeiro, especifique o nome da tabela na qual deseja criar o índice após as ALTER TABLE palavras-chave.
Segundo, use a ADD FULLTEXT cláusula para definir o FULLTEXT índice para uma ou mais colunas da tabela.

O exemplo a seguir cria a tabela posts e adiciona um índice de texto completo usando a ALTER TABLEinstrução:

Primeiro, crie a poststabela:

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Observe que você deve eliminar a tabela posts se a tiver criado antes de usar a DROP TABLEinstrução antes de executar a CREATE TABLEinstrução acima.

Segundo, adicione um índice de texto completo à poststabela:

ALTER TABLE posts
ADD FULLTEXT(title, body);

Adicionando um índice de texto completo a uma tabela usando a instrução CREATE INDEX

É possível usar a CREATE FULLTEXT INDEXinstrução para definir um índice de texto completo nas colunas de uma tabela da seguinte forma:

CREATE FULLTEXT INDEX index_name
ON tbl_name(column1, column2,...);

Por exemplo, o seguinte usa a CREATE FULLTEXT INDEXinstrução para criar um índice de texto completo para a coluna titlee bodyda poststabela:

CREATE FULLTEXT INDEX idx_fts
ON posts(title, body);

Nesta instrução, o nome do índice de texto completo é idx_fts.

Removendo um índice de texto completo

Para remover um FULLTEXTíndice, você usa a ALTER TABLE ... DROP INDEXinstrução:

ALTER TABLE tbl_name
DROP INDEX index_name;

Por exemplo, a instrução a seguir exclui o idx_ftsíndice da poststabela:

ALTER TABLE posts
DROP INDEX idx_fts;

Um exemplo completo de índice de texto completo do MySQL

Primeiro, crie uma poststabela com um índice de texto completo que inclua as colunas titlee :body

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT,
  FULLTEXT KEY (body, title)
);Linguagem de código:  PHP  ( php )

Segundo, insira algumas linhas na poststabela:

INSERT INTO posts (title, body) VALUES
('Introduction to MySQL', 'MySQL is an open-source relational database management system.'),
('MySQL Data Types', 'MySQL supports various data types, including INT, VARCHAR, and TEXT.'),
('MySQL Indexing', 'Proper indexing is crucial for optimizing MySQL query performance.'),
('MySQL Joins', 'Learn about different types of joins in MySQL, such as INNER JOIN and LEFT JOIN.'),
('MySQL Security Best Practices', 'Secure your MySQL database by following recommended security practices.'),
('MySQL Backup and Restore', 'Regularly backup your MySQL database to prevent data loss.'),
('MySQL Stored Procedures', 'Create reusable SQL code with MySQL stored procedures.'),
('MySQL Transactions', 'Ensure data consistency and integrity using MySQL transactions.'),
('MySQL Performance Tuning', 'Optimize the performance of your MySQL database for better efficiency.'),
('MySQL Tools and Utilities', 'Explore tools like MySQL Workbench for managing and interacting with MySQL databases.');
Linguagem de código:  JavaScript  ( javascript )

Terceiro, realize uma pesquisa de texto completo usando as funções MATCH()eAGAINST() para encontrar as postagens que contêm a palavra database:

SELECT 
  id, 
  title, 
  body 
FROM 
  posts 
WHERE 
  MATCH (title, body) AGAINST (
    'database' IN NATURAL LANGUAGE MODE
  );
Linguagem de código:  JavaScript  ( javascript )

Saída:

+----+-------------------------------+-------------------------------------------------------------------------+
| id | title                         | body                                                                    |
+----+-------------------------------+-------------------------------------------------------------------------+
|  1 | Introduction to MySQL         | MySQL is an open-source relational database management system.          |
|  5 | MySQL Security Best Practices | Secure your MySQL database by following recommended security practices. |
|  6 | MySQL Backup and Restore      | Regularly backup your MySQL database to prevent data loss.              |
|  9 | MySQL Performance Tuning      | Optimize the performance of your MySQL database for better efficiency.  |
+----+-------------------------------+-------------------------------------------------------------------------+
4 rows in set (0.01 sec)Linguagem de código:  JavaScript  ( javascript )

Resumo

Use FULLTEXTpara definir um índice de texto completo que inclua uma ou mais colunas na CREATE TABLE instrução.
Use ALTER TABLE ... ADD FULLTEXT a instrução para adicionar um FULLTEXT índice a uma tabela existente.
Use CREATE FULLTEXT INDEX a instrução para criar um índice para uma ou mais colunas de uma tabela existente.
Use ALTER TABLE ... DROP INDEX a instrução para remover um FULLTEXT índice existente.