Hoy tuve la necesidad de realizar la búsqueda de una cadena en una columna de una tabla MySQL ignorando las reglas de comparación impuestas por el collation, con el objetivo de obtener sólo coincidencias exactas.



Una inconsistencia entre una aplicación y una base de datos, provocada por el uso de conjuntos de caracteres incompatibles (la base de datos utilizaba el conjunto de caracteres latin-1, mientras que la aplicación insertaba las tuplas con las cadenas codificadas en utf-8), hizo que los strings en la base de datos queden mal codificados, distorsionando la visualización luego desde la aplicación.

Este es un error muy común, y es cuando aparecen esos caracteres à en lugar de caracteres con tilde o eñes.

El problema es que, al buscar utilizando una consulta SELECT, el collation hace que se interprete el caracter 'Ã' como una simple letra 'a':

mysql> select nombre from categoria where nombre like '%Ã%' limit 5;
+------------------------+
| nombre                 |
+------------------------+
| Alimentos y bebidas    |
| Almacén                |
| Autoservicio           |
| Despensa               |
| Distribuidor mayorista |
+------------------------+
5 rows in set (0.01 sec)

Entonces es necesario ignorar el collation utilizando el operador BINARY, el cual interpreta a los strings de forma binaria:

mysql> select nombre from categoria where binary nombre like '%Ã%' limit 5;
+--------------------------------------------------+
| nombre                                           |
+--------------------------------------------------+
| Antigüedades                                    |
| Antigüedades - Restauracion Y Reparacion        |
| Automotores - Caños De Escape Y Silenciadores   |
| Automotores - Repuestos Citroën                 |
| Aviacion - Compañias                            |
+--------------------------------------------------+
5 rows in set (0.00 sec)

Esto me permitió identificar los caracteres corrompidos.

¿Cómo corregir este desastre?

Luego de unificar los conjuntos de caracteres utilizados (cambiando collations en la base de datos y tablas si es necesario), existen muchas formas de resolver este inconveniente. Es decir, pasar a utf-8 strings mal codificados como latin-1. Para mí lo más simple es hacer un volcado de la base de datos y editar con sed.

Suponiendo que la base de datos se llama "webapp", hacer un dump:

root@cloud:~# mymysqldump.sh webapp
root@cloud:~# grep à webapp.sql | wc -l
1

Se observa que existen caracteres 'Ã'. Se contabiliza únicamente una línea porque en este caso el INSERT es compacto, no extendido.

Reemplazar ñ por ñ:

root@cloud:~# cat webapp.sql | sed -e 's/ñ/ñ/g' > webapp2.sql

Reemplazar ë por ë:

root@cloud:~# cat webapp2.sql | sed -e 's/ë/ë/g' > webapp3.sql

Reemplazar ü por ü:

root@cloud:~# cat webapp3.sql | sed -e 's/ü/ü/g' > webapp4.sql

Seguir el mismo procedimiento hasta que desaparezcan los caracteres Ã:

root@cloud:~# grep à webapp4.sql | wc -l
0

Luego, restaurar la base desde el volcado corregido:

root@cloud:~# mysql -u root -p --default-character-set=utf8
mysql> use webapp;
mysql> source webapp4.sql

Finalmente verificar que no aparezcan estos caracteres "extraños":

mysql> select nombre from categoria where binary nombre like '%Ã%';
Empty set (0.01 sec)

Referencias

MySQL 5.7 Reference Manual - The BINARY Operator

MySQL 5.7 Reference Manual - String Comparison Functions

MySQL 5.7 Reference Manual - Character Sets and Collations in General

mymysqldump.bash

MySQL 5.7 Reference Manual - Table Character Set and Collation

MySQL 5.7 Reference Manual - Database Character Set and Collation


Tal vez pueda interesarte


Compartí este artículo