Trabajar con los caracteres en las bases de datos parece que no tiene consecuencias, hasta que te topas con cosas que no concuerdan. En la entrada de hoy os cuento cómo hacer más fácil cambiar los COLLATE de las tablas todas al mismo.
El motivo de esta entrada
El motivo de esta entrada es que al hacer una consulta, no obtenía el resultado correcto. Reproducimos lo que me pasó, y creamos una base de datos, tal que así:
CREATE DATABASE `test` /*!40100 COLLATE ‘utf8_general_ci’ */;
Y creamos una tabla:
CREATE TABLE `test` (`campo` VARCHAR(50) NULL)
COLLATE=’utf8_general_ci’
ENGINE=InnoDB ;
Insertamos dos datos:
INSERT INTO `test`.`test` (`campo`) VALUES (‘cómo’);
INSERT INTO `test`.`test` (`campo`) VALUES (‘comodin’);
Y hacemos la siguiente consulta:
SELECT * FROM test WHERE campo LIKE ‘com%’
Puedes comprobar que salen los dos registros insertados:
SELECT * FROM test
En realidad, el resultado no es el esperado, porque sólo nos debería salir el segundo registro. El motivo por el que esto sucede está relacionado con el juego de caracteres utilizado. Para obtener el resultado deseado, creamos la tabla test2:
CREATE TABLE `test2` (
`campo` VARCHAR(50) NULL COLLATE ‘utf8_bin’
)
COLLATE=’utf8_spanish_ci’
ENGINE=InnoDB ;
INSERT INTO `test`.`test2` (`campo`) VALUES (‘cómo’);
INSERT INTO `test`.`test2` (`campo`) VALUES (‘comodin’);
La única diferencia entre las dos tablas está en COLLATE en ‘campo’. Ahora, se puede comprobar que el resultado es el correcto.
Error 1267 – Illegal mix of collations
Pero la circunstancia anterior no es la única donde puedes tener problemas con los juegos de caracteres. El error 1267 de MySQL se debe a que el motor de MySQL encuentra un problema al intentar comparar dos cadenas codificadas con diferente juego de caracteres, y claro …. así, imposible.
La solución: modificar todos los COLLATE
Si necesitas implementar todos los cambios de COLLATE en tu base de datos y en todos los campos de cadenas de texto, hay que crear unas cuantas sentencias de alteración de tablas, para lo cual, es ciertamente beneficioso utilizar information_schema para ello. Te dejo dos consultas que te pueden ayudar.
TLa siguiente consulta permite preparar las sentencias de modificación de la codificación de las cadenas, consulta que almanecamos con el nombre de fix_collate:
# COLLATION para DATABASES
(
SELECT CONCAT(‘ALTER DATABASE ‘,SCHEMA_NAME,’ CHARACTER SET utf8 COLLATE utf8_spanish_ci;’) as smtp, SCHEMA_NAME as esquema
FROM information_schema.SCHEMATA S
WHERE ( DEFAULT_CHARACTER_SET_NAME != ‘utf8’ OR DEFAULT_COLLATION_NAME != ‘utf8_bin’)
AND SCHEMA_NAME not in (‘performance_schema’, ‘mysql’, ‘lib’,’common_schema’, ‘information_schema’)
)
UNION
# COLLATION para TABLAS
(
SELECT CONCAT(“ALTER TABLE “, TABLE_SCHEMA, “.”, TABLE_NAME,” COLLATE utf8_spanish_ci;”) AS smtp, TABLE_SCHEMA as esquema
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE=”BASE TABLE”
AND TABLE_COLLATION != ‘utf8_spanish_ci’
and TABLE_SCHEMA not in (‘performance_schema’, ‘mysql’, ‘lib’,’common_schema’, ‘information_schema’)
)
Si ejecutamos la consulta para la base de datos ‘test’, obtenemos:
Para la ejecución de todas las consultas ALTER TABLE, simplemente copiamos el resultado de la columna smtp, y la ejecutamos:
Es también recomendable ejecutar la modificación de los COLLATE también para los campos de texto, para lo que utilizamos otra consulta:
(SELECT CONCAT( ‘ALTER TABLE ‘, c.TABLE_SCHEMA, ‘.’, c.TABLE_NAME , ‘ CHANGE ',COLUMN_NAME, ' ',COLUMN_NAME, ' ‘, DATA_TYPE , ‘(‘, CHARACTER_MAXIMUM_LENGTH , ‘) CHARACTER SET utf8 COLLATE utf8_spanish_ci ;’ ) as stmp, c.TABLE_SCHEMA as esquema
from information_schema.COLUMNS c
INNER JOIN information_schema.TABLES t ON (c.table_schema = t.table_schema and c.table_name = t.table_name)
WHERE t.TABLE_TYPE=”BASE TABLE” and
t.TABLE_SCHEMA not in (‘performance_schema’, ‘mysql’, ‘lib’,’common_schema’, ‘information_schema’,’crosstab’)
and collation_name != ‘utf8_spanish_ci’
AND DATA_TYPE not in (‘longtext’, ‘text’, ‘tinytext’,’mediumtext’) )
UNION
(
SELECT CONCAT( ‘ALTER TABLE ‘, c.TABLE_SCHEMA, ‘.’, c.TABLE_NAME,’ CHANGE ',COLUMN_NAME,' ',COLUMN_NAME, ' ‘,DATA_TYPE, ‘ CHARACTER SET utf8 COLLATE utf8_spanish_ci ;’ ) as stmp, c.TABLE_SCHEMA as esquema
from information_schema.COLUMNS c
INNER JOIN information_schema.TABLES t ON (c.table_schema = t.table_schema and c.table_name = t.table_name)
WHERE t.TABLE_TYPE=”BASE TABLE” and
t.TABLE_SCHEMA not in (‘performance_schema’, ‘mysql’, ‘lib’,’common_schema’, ‘information_schema’,’crosstab’)
and collation_name != ‘utf8_spanish_ci’
AND DATA_TYPE in (‘longtext’, ‘text’, ‘tinytext’,’mediumtext’)
)
Esta consulta se llama fix_collate_columns. Y ejecutamos el resultado para modificar finalmente el collate de todo, tablas y campos.
Espero les sea de utilidad estas consultas.