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. A seguir conheceremos como criar índices FullText em uma tabela no MySQL.
Como criar Índices FullText
Podemos indexar colunas de tabelas do MySQL da mesma forma na qual criamos os índices KEY, PRIMARY KEY e UNIQUE. Os índices FULLTEXT podem ser compostos e contar com mais de uma coluna. Para que uma coluna seja utilizada pelas funções de busca de termos, você precisa ter um índice para cada coluna envolvida na busca, ou mesmo um índice abrangendo duas ou mais colunas de forma a implementar um índice composto.
Caso seja criado um índices para cada coluna, poderemos buscar em cada coluna uma string que desejarmos obter os resultados, mas ao criar um índice envolvendo duas ou mais colunas, ao escrever a sua SELECT, lembre-se de abordar todas as colunas que fazem parte deste índice. A Listagem 3 mostra como criar uma tabela com colunas indexadas com o mecanismo FULLTEXT SEARCH.
mysql> CREATE TABLE articles (
-> id int not null auto_increment primary key,
-> titulo char(60) not null,
-> texto text,
-> FULLTEXT(titulo),
-> FULLTEXT(texto)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.13 sec)
