Working with the characters in the databases seems to have no consequences, until you come across things that do not match. In today’s post I tell you how to make it easier to change the COLLATE of the tables all to it.
The reason for this entry
The reason for this entry is that when making a query, it did not get the correct result. We reproduce what happened to me, and create a database, such that:
CREATE DATABASE `test` /*!40100 COLLATE ‘utf8_general_ci’ */;
so, let’s create a new table:
CREATE TABLE `test` (`campo` VARCHAR(50) NULL)
COLLATE=’utf8_general_ci’
ENGINE=InnoDB ;
And insert some data:
INSERT INTO `test`.`test` (`campo`) VALUES (‘cómo’);
INSERT INTO `test`.`test` (`campo`) VALUES (‘comodin’);
Let’s create the next sentence:
select * from test where campo like ‘com%’
as you can check, the output is the tow records inserted:
SELECT * FROM test
The output is not the one expected, because the second record is only the answer expected, and this problem is related to the character set used. In order to get the right result, let’s create a new table 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’);
The only difference between both tables are the COLLATE on ‘campo’ field. Now, the result is correct:
Error 1267 – Illegal mix of collations
But the previous circumstance is not the only one where you can have problems with character sets. The error 1267 of MySQL is due to the fact that the MySQL engine encounters a problem when trying to compare two encoded strings with different character sets, and of course …. well, impossible..
The solution: modify all COLLATE
If you need to implement all the changes of COLLATE in your database and in all the text string fields, you have to create a few table alteration sentences, for which it is certainly beneficial to use information_schema for it. I leave you two questions that can help you.
The following query allows you to prepare the statements to modify the encoding of the strings, see that we are working with the name of 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’)
)
If you execute the query for the ‘test’ database, you will get:
For the execution of all alter tables, let’s copy the result of column smtp, and let’s execute them:
It is also advisable to execute the modification of the COLLATE also for the text fields, for which we use another query:
(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’)
)
I called this query fix_collate_columns. And you only need to execute the outcome of the query for modifying the COLLATE of tables and fields.
I hope these queries will be useful for you!