BÚSQUEDA FUZZY EN MYSQL: CÓMO CREAR UN BUSCADOR

• Uso del operador LIKE con comodines: El operador LIKE con comodines ("%" o "_") es la forma más básica de realizar una búsqueda fuzzy en MySQL. Aunque no es tan avanzada como otras técnicas, es sencilla y útil para coincidencias parciales. Ejemplo:
  
  

  SELECT * FROM productos WHERE nombre LIKE '%manzana%';
  //Esta query devuelve las filas que contienen "manzana" en cualquier parte del campo nombre, como "manzanas", "jugo de manzana", etc.
  

  Este método es fácil de implementar y no requiere índices especiales. Pero no maneja bien errores tipográficos (e.g., "manzna" no coincidirá con "manzana") y puede ser lento en tablas grandes si no hay índices adecuados.
• Función SOUNDEX: SOUNDEX realiza búsquedas fonéticas, es decir, encuentra palabras que suenan similar en inglés. Es útil para nombres o términos con variaciones ortográficas. Ejemplo:
  
  

  SELECT * FROM clientes WHERE SOUNDEX(nombre) = SOUNDEX('Jon');
  //Esta query  puede devolver "Jon", "John", "Sean" o "Shawn", ya que suenan similar.
  

  Este método es ideal para nombres o palabras con pronunciaciones similares, es nativo en MySQL y no requiere configuraciones adicionales. Aunque está diseñado principalmente para inglés y es menos efectivo para otros idiomas como el español tampoco maneja bien palabras largas o con diferencias al final. Requiere que la primera letra sea idéntica para coincidir. Para una optimización sería considerar crear un índice en una columna calculada con SOUNDEX para mejorar el rendimiento:

  LTER TABLE clientes ADD nombre_soundex VARCHAR(4) GENERATED ALWAYS AS (SOUNDEX(nombre)) STORED;
  CREATE INDEX idx_nombre_soundex ON clientes(nombre_soundex);
  

• FuilText: MySQL ofrece índices de texto completo (FULLTEXT) para búsquedas más avanzadas en columnas de tipo CHAR, VARCHAR o TEXT. Aunque no es una búsqueda difusa pura, puede configurarse para ser más flexible. Muy eficiente con índices FULLTEXT, soporta relevancia para ordenar resultados, modo booleano permite operadores como "+", " -", "*". Pero solo está disponible en tablas con motores InnoDB o MyISAM, palabras cortas (menor a 3 o 4 caracteres, según configuración) son ignoradas, no maneja errores tipográficos complejos. Los ejemplos de FullText los tenéis en el post FuilText.
• Distancia de Levenshtein: La distancia de Levenshtein en MySQL mide el número de ediciones (inserciones, eliminaciones o sustituciones) necesarias para transformar una cadena en otra. MySQL no tiene esta función de forma nativa, pero puedes implementar una función definida por el usuario (UDF) o calcularla en la aplicación. Los ejemplos de distancia de Levenshtein en MySQL los podéis ver Distancia de Levenshtein. Muy preciso para manejar errores tipográficos y flexible para definir el nivel de tolerancia. Pero no es nativo, requiere instalar una UDF y puede ser lento en tablas grandes sin optimización.. Su escalabilidad limitada para grandes conjuntos de datos.
• Trigramas: Los trigramas dividen una cadena en grupos de tres caracteres consecutivos, permitiendo búsquedas difusas basadas en similitud. MySQL no tiene soporte nativo para trigramas, pero puedes implementarlos manualmente. Ejemplo:
  
  

  CREATE FUNCTION TRIGRAM_SEARCH(search_string VARCHAR(255), target_string VARCHAR(255))
  RETURNS FLOAT DETERMINISTIC
  BEGIN
      DECLARE i INT DEFAULT 1;
      DECLARE total_trigrams INT DEFAULT 0;
      DECLARE matched_trigrams INT DEFAULT 0;
      DECLARE search_length INT;
      DECLARE target_length INT;
      SET search_length = CHAR_LENGTH(search_string);
      SET target_length = CHAR_LENGTH(target_string);
  
      IF search_length < 3 OR target_length < 3 THEN
     RETURN 0;
      END IF;
  
      CREATE TEMPORARY TABLE search_trigrams (trigram VARCHAR(3));
      CREATE TEMPORARY TABLE target_trigrams (trigram VARCHAR(3));
  
      WHILE i <= search_length - 2 DO
     INSERT INTO search_trigrams VALUES (SUBSTRING(search_string, i, 3));
     SET i = i + 1;
      END WHILE;
  
      SET i = 1;
      WHILE i <= target_length - 2 DO
     INSERT INTO target_trigrams VALUES (SUBSTRING(target_string, i, 3));
     SET i = i + 1;
      END WHILE;
  
      SELECT COUNT(DISTINCT t1.trigram) INTO matched_trigrams
      FROM search_trigrams t1
      JOIN target_trigrams t2 ON t1.trigram = t2.trigram;
  
      SELECT COUNT(DISTINCT trigram) INTO total_trigrams
      FROM search_trigrams;
  
      DROP TEMPORARY TABLE search_trigrams;
      DROP TEMPORARY TABLE target_trigrams;
  
      IF total_trigrams > 0 THEN
     RETURN matched_trigrams / total_trigrams;
      ELSE
     RETURN 0;
      END IF;
  END;
  //Luego ejecutar la función
  SELECT nombre FROM productos WHERE TRIGRAM_SEARCH(nombre, 'manzana') > 0.6;
  

  Los trigramas son efectivos para errores tipográficos y coincidencias parciales y son más flexibles que SOUNDEX para idiomas no ingleses. Pero requiere una implementación personalizada. y puede ser intensivo en recursos para grandes conjuntos de datos.