Buscador con MySQL utilizando tablas InnoDB

Posted by: matubaum on: 11 octubre, 2007

• In: Programación
• 9 Comments

Hace poco estaba haciendo un buscador para mi próximo sitio web anunciegratis.com.ar, que todavía no está online pero lo estará a la brevedad; y me encontré con un pequeño problema.
Yo ya conocía el tipo de consultas utilizando índices
FULLTEXT, que es la forma ideal para implementar un buscador.
Pero lo que no sabía es que este tipo de consultas, no se puede utilizar cuando usamos tablas innoDB. Sólo está permitido en tablas MyISAM (como lo especifica el manual de MySQL).
Esto hizo que me las tenga que ingeniar de alguna otra forma, ya que no pensaba sacrificar todo mi modelo relacional, pasando todas mis tablas a MyISAM, solamente para que me funcionen las consultas «SELECT … MATCH()… AGAINST()…».
En este artículo voy a explicar una técnica para realizar consultas de búsquedas usando tablas innoDB, si alguien conoce una forma mejor, no duden en contactarse.

Lo que hice fue una función de base de datos «buscarEnTexto» [ que se puede ver más abajo].

• Recibe dos parametros:
  • textoFuente: Es el texto en donde se realiza la búsqueda.
  • parametros: Es la concatenación de todas las palabras a buscar separadas por coma. Ejemplo: «autos,nuevos,o,usados» , «ropa,deportiva» .
  • devuelve 1 si todas las palabras contenidas en parametros están contenidas en la cadena textoFuente. Esto tuve que hacerlo así ya que en MySQL, las funciones no pueden tener infinitos parámetros o un array de parámetros, lo que complica un poco las cosas.
• Consideraciones:
  • La función no distingue mayúsculas de minúsculas.
  • Para generar la cadena parametros, deberá hacerlo mediante algún lenguaje como PHP, a partir de la cadena de búsqueda ingresada por el usuario. Ejemplo:
    Si la cadena de búsqueda es «autos usados y nuevos», deberá convertirla en «autos,usados,y,nuevos».
• Descripción del funcionamiento: Lo primero que hace es crear una tabla temporal «tparametros» e inserta cada palabra contenida en parametros en una fila distinta de esta tabla. Luego realiza una consulta por cada fila en tparametros para ver si la palabra de esa fila está contenida en textoFuente. Si alguna de las palabras no está contenida en textoFuente sale inmediatamente de la funcion devolviendo 0. Si todas las palabras estan contenidas la funcion devuelve 1.
• Ejemplo de uso: (buscando cadena autos usados y nuevos )
  • #BUSCAR EN CADENA «vendo autos nuevos»:
    SELECT * FROM tabla1 t1 WHERE buscarEnTexto("vendo autos nuevos", "autos,usados,y,nuevos") = 1 //devolverá 0 ya que ni la palabra "usados" ni "y" aparecen en el primer parámetro.

  • #BUSCAR EN CADENA «vendo autos usados y nuevos»:
    SELECT * FROM tabla1 t1 WHERE buscarEnTexto("vendo autos usados y nuevos", "autos,usados,y,nuevos") = 1 //devolverá 1

  • #BUSCAR EN CAMPO TITULO Y DESCRIPCION DE UNA TABLA «t1»:
    SELECT * FROM tabla1 t1 WHERE buscarEnTexto(CONCAT(t1.titulo,t1.descripcion), "autos,usados,y,nuevos") = 1

MySQL


CREATE FUNCTION `buscarEnTexto`(textoFuente TEXT, parametros VARCHAR(1000))
RETURNS tinyint(4)
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT 'Recibe la cadena de parametros y valida que todas las palabras estén incluidas en textoFuente'
BEGIN

DECLARE posicion INTEGER ;
DECLARE done INT DEFAULT 0;
DECLARE palabra VARCHAR(1000);
DECLARE encontrada VARCHAR(1000);
DECLARE parametro varchar(1000);
DECLARE resp TINYINT DEFAULT 0 ;
DECLARE i CURSOR FOR SELECT cparametro FROM tparametros;
DECLARE busqueda cursor FOR SELECT textoFuente LIKE CONCAT('%',palabra,'%') AS contiene;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

CREATE TEMPORARY TABLE IF NOT EXISTS tparametros (cparametro varchar(1000));
DELETE FROM tparametros;

SET parametros = CONCAT(parametros , ',');

WHILE LOCATE(',',parametros ) 0 DO

SET posicion = LOCATE(',',parametros ) ;

SET parametro = left(parametros, posicion - 1) ;

INSERT INTO tparametros values (TRIM(parametro));

SET parametros = SUBSTRING(parametros,posicion+1) ;

END WHILE;
OPEN i;

REPEAT
FETCH i INTO palabra;
IF NOT done THEN

OPEN busqueda;
FETCH busqueda INTO encontrada;

CLOSE busqueda;
IF encontrada THEN
SET resp = 1;
ELSE
RETURN 0;
END IF;
END IF;
UNTIL done END REPEAT;
RETURN resp;
END;