Este é um post disponível para assinantes MVPou para quem possui Créditos DevMedia. Clique aqui para saber mais!
Artigo SQL Magazine 67 - MySQL FullText Search
Artigo publicado Revista SQL Magazine 67.
Você não gostou da qualidade deste conteúdo?
(opcional) Você gostaria de comentar o que não lhe agradou?
MySQL FullText Search
Desmistificando a busca com FullText Search
Quem ainda não viu aquelas buscas baseadas em termos, palavras ou mesmo uma frase que são realizadas em sistemas como Wikipédia ou outros portais de conteúdo? Você nunca precisou implementar algo parecido em sua base de dados para dar suporte a este tipo de em meio a grandes quantidades de textos? Em algum momento você pode precisar disso.
Com este artigo, desmistificaremos o mecanismo que nos possibilita extrair informações segundo sua relevância (vale salientar que o Wikipédia é um dos grandes utilizadores do servidor de bancos de dados MySQL, quebrando o paradigma que este é somente bem vindo para pequenas aplicações).
Saindo da analogia e vindo para a vida real, imagine que você trabalhe com uma base de dados que dá suporte a uma grande quantidade de dados referentes a artigos que são depositados e publicados em uma revista do grupo DEVMEDIA, a SQL MAGAZINE. A maioria destes artigos é escrita por um ou vários autores de várias partes do mundo.
Em um determinado momento, você precisa efetuar uma busca que lhe possibilite recuperar alguns dos artigos com base em termos, palavras e /ou frases definidas pelo usuário, na interface de seu sistema que se conecta a este repositório. Por exemplo, queremos localizar artigos que tenham mais a palavra “MySQL” e menos a palavra “SQL Server”, ou que tenham mais a palavra “replicação” e menos a palavra “ORACLE”.
Para que essa busca possa ser efetiva e nos retorne o resultado esperado e consistente, o MySQL suporta, em tabelas controladas pelo Storage Engine MyISAM, o mecanismo de indexação de textos que é realizado palavra por palavra, conhecido como FULLTEXT SEARCH. Através de colunas indexadas com índices FULLTEXT ou FULLTEXT INDEX, é possível efetuar a recuperação de dados aplicando regras ao comando SELECT que definem uma melhor busca e extração dos dados diante da relevância dos termos, palavras ou frases a serem procuradas. Como complemento a este recurso, existem algumas funções que nos auxiliam em tal extração.
O MySQL dá suporte a este mecanismo desde versões bem antigas, o que assegura que se você estiver utilizando um release mais atual de uma das versões 4.1, 5.0, 5.1, 5.4 ou 6.0. Ao longo deste artigo iremos explorar tal recurso com maiores detalhes, mostrando exemplo de sua aplicação em diferentes situações.
Conhecendo o FullText Search
Diferentemente de quando precisamos buscar valores exatos utilizando o sinal de igualdade ou ainda buscar strings que comecem, terminem ou contenham uma determinada substring através de alguma estrutura provida pela da linguagem SQL, o FULLTEXT SEARCH é um mecanismo que nos permite procurar termos específicos no formato string em meio a grandes quantidades de texto que estejam localizados em colunas de tabelas de um banco de dados. Estes textos, ao serem cadastrados em tais colunas, terão cada uma de suas palavras indexadas por índices chamados FULLTEXT INDEX, que também são parte do mecanismo que nos possibilita realizar a busca. Existem algumas implicações especiais para que palavras de determinados tamanhos ou número de caracteres possam fazer parte do índice, o que veremos ao longo deste artigo. Outros SGBD’s contemplam o mecanismo de FullText Search, tais como o SQL Server, ORACLE e PostgreSQL, mas neste artigo falaremos especificamente deste recurso no MySQL.
Ao criar tabelas em seu banco de dados MySQL e tendo em mente que haverá na interface de seu sistema um campo para busca por termos, palavras e/ou frases, baseada em uma coluna de uma tabela que armazena grande quantidade de texto, podemos utilizar nestas colunas índices do tipo FULLTEXT, sendo possível a utilização também das funções disponíveis MATCH() e AGAINST().
Existem três tipo de buscas por termos no MySQL:
· BOOLEAN FULL-TEXT SEARCH:
· NATURAL LANGUAGE FULLTEXT SEARCHES;
· FULLTEXT SEARCH WITH QUERY EXPANSION;
Explicaremos mais à frente o significa cada busca com detalhes e exemplos práticos para testamos cada um dos tipos. Antes, precisamos criar o nosso cenário, procurando enfatizar como criar uma tabela com tais índices.
Vale lembrar para o leitor que o mecanismo de busca por termos FULLTEXT SEARCH somente está disponível no SGBD MySQL em tabelas que são controladas pelo Storage Engine MyISAM. Caso haja a tentativa de criar índices deste tipo em tabelas controladas por outros Engines, como por exemplo InnoDB, um erro será enviado ao usuário, como mostra a Listagem 1.
Listagem 1. Erro ao tentar utilizar o mecanismo FULLTEXT em uma tabela InnoDB.
mysql> CREATE TABLE t1 (
-> id int not null auto_increment primary key,
-> texto text,
-> FULLTEXT(texto)
-> ) ENGINE=INNODB;
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
"
Este é um post disponível para assinantes MVPou para quem possui Créditos DevMedia. Clique aqui para saber mais!
Wagner Bianchi
Wagner Bianchi é Tecnólogo em Gerenciamento de Bancos de Dados pela Faculdade Infórium de Tecnologia, Pós-Graduando em Administração Estratégica de Empresas (Executivo Jr.) pela Fundação Getúlio Vargas no Minas Business Institute, Consultor em Desenvolvimento de Sistemas pela INFODBA C&T, empresa on...
