viernes, 11 de febrero de 2011

Búsquedas por campos de texto en MySQL

Versión: MySQL 5.1.x

Hace un tiempo publiqué un post sobre el problema de búsquedas por campos de texto usando PostgreSQL:
http://romanmussi.blogspot.com/2009/12/busquedas-por-campos-de-texto-en.html

Hoy vamos a considerar la misma cuestión en MySQL.

El problema

Supongamos que nos piden hacer una búsqueda flexible por nombre de cliente. La idea es que el usuario pueda ingresar como criterio de búsqueda un nombre o parte del nombre sin necesidad de considerar mayúsculas, minúsculas y letras acentuadas.

La solución en MySQL

Desde el punto de vista del código la solución en MySQL puede ser muy sencilla y prácticamente transparente:

SELECT * FROM clientes WHERE nombre LIKE ‘%martin%’

Se usa LIKE, estándar SQL, para comparar una cadena de texto con un determinado patrón. Y se usa % que representa cero o más caracteres para hacer más flexible la comparación (permite hacer la búsqueda parcial).

Esto debería recuperar clientes de nombre:
MARTIN, SAN MARTIN, San Martin, Martín, San Martín, Etc.

¿Listo? ¿Ya podemos volver a jugar al Berusky? Mmmm… no tan rápido. Para que esta búsqueda funcione tenemos que comprender mejor como hace MySQL para realizar las comparaciones entre textos.

CHARACTER SET y COLLATE

Lo principal es comprender lo que son los conjuntos de caracteres y los “cotejos” (1). Un conjunto de caracteres es un conjunto de símbolos y su correspondiente codificación. Un cotejo es un conjunto de reglas para comparar los símbolos de un conjunto de caracteres (2).

El servidor MySQL soporta muchos conjuntos de caracteres. Los más conocidos en el mundo hispanoparlante son LATIN1 y UTF8. A su vez, un conjunto de caracteres tiene siempre como mínimo un cotejo, aunque puede tener varios. Por ejemplo, el conjunto de caracteres LATIN1 tiene los siguientes cotejos:

latin1_german1_ci (Alemán DIN-1)
latin1_swedish_ci (Sueco/Finlandés)
latin1_general_ci (Multilingüe)
latin1_general_cs (Multilingüe, sensible a mayúsculas)
latin1_spanish_ci (Español moderno)

Como se observa, existe una convención para nombres de cotejos: comienzan con el nombre del conjunto de caracteres al que están asociados, luego incluyen el nombre del idioma, y terminan con _ci (no distingue entre mayúsculas y minúsculas), _cs (distingue entre mayúsculas y minúsculas), o _bin (binario).

El cotejo le indica al motor el modo en que debe compara los símbolos de un conjunto de caracteres. Veamos como ejemplo que dicen algunos cotejos de uso habitual sobre los caracteres que deben ser considerados equivalentes a la letra “i” (i minúscula).

Para el cotejo latin1_general_ci la “i” es equivalente a: “I” (i mayúscula). Esto significa que el motor solo va a considerar como equivalente de la i minúscula a la i mayúscula.

Para el cotejo latin1_spanish_ci la “i” es equivalente a: “I Ì Í Î Ï ì í î ï”. En este caso el motor va a considerar a la i minúscula como equivalente de un conjunto mucho más amplio de opciones.

Aplicando las reglas anteriores si el motor evalúa: ‘Martín’ LIKE ‘%martin%’
• devuelve falso en latin1_general_ci
• devuelve verdadero en latin1_spanish_ci

Para consultar el detalle de reglas de equivalencia de cada cotejo es muy útil el sitio:
http://www.collation-charts.org/

Una vez que entendemos como MySQL realiza la comparación entre caracteres simplemente debemos asegurarnos que utilice el cotejo que se ajusta a nuestras necesidades, y eso depende de las configuraciones. Hay configuraciones por defecto para conjuntos de caracteres y cotejos en cuatro niveles: servidor, base de datos, tabla, y conexión. Y existen configuraciones específicas para campos, por ejemplo. Puede consultar en detalle como se establecen las configuraciones y como se evalúan las precedencias en el Manual de MySQL (2). Aquí simplemente mencionaremos dos formas de forzar el uso de un cotejo específico para resolver el problema de la búsqueda de manera sencilla.

1) Puede definir el conjunto de caracteres y cotejo por defecto de la columna

Cada columna de tipo “carácter” (CHAR, VARCHAR, o TEXT) tiene un conjunto de caracteres y cotejo de columna. En general la configuración por defecto de la columna tiene la mayor precedencia al momento de efectuar la comparación lo que resuelve nuestros problemas. La sintaxis de definición de columnas tiene cláusulas opcionales para especificar el conjunto de caracteres y la colación.

Ejemplo:

CREATE TABLE clientes
(
nombre VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_spanish_ci
);

Puede modificar esos valores en una columna ya existente mediante phpMyAdmin ó el comando ALTER TABLE, según le resulte más cómodo.

2) Puede especificar el cotejo a utilizar al definir la consulta

Si no desea modificar las definiciones por defecto en la base de datos puede especificar el cotejo a utilizar al definir la consulta SQL, del siguiente modo:

SELECT * FROM clientes
WHERE nombre LIKE ‘%martin%’ COLLATE latin1_spanish_ci

En este caso solo debe tener la precaución de que el conjunto de caracteres coincida con el definido en la columna (latin1, utf8, etc.).

Con cualquier de estos dos procedimientos logrará hacer funcionar las consultas como necesita.

Notas

(1) En el Manual de MySQL 5.0 en Español se traduce COLLATE como “colación”. Aquí hemos preferido “cotejo” por considerarlo más adecuado ó amigable. De todos modos es una cuestión de gustos.

(2) Para un análisis en profundidad de conjuntos de caracteres y cotejamiento véase:

Manual de MySQL 5.1 (Inglés)
Chapter 9. Internationalization and Localization
http://dev.mysql.com/doc/refman/5.1/en/internationalization-localization.html

Manual de MySQL 5.0 (Español, corresponde a una versión anterior pero sigue siendo útil para el tema)
Capítulo 10. Soporte de conjuntos de caracteres
http://dev.mysql.com/doc/refman/5.0/es/charset.html