Infiniroot Blog: We sometimes write, too.

Of course we cannot always share details about our work with customers, but nevertheless it is nice to show our technical achievements and share some solutions.

How to change the collation of a MySQL database

Published on December 11th 2013


Sometimes databases have a different character collation than its tables. This could happen under several circumstances, for example the database is created without definition of the collation and then the tables were created with a collation setting:

mysql> CREATE DATABASE test;
mysql> CREATE TABLE table CHARACTER SET latin1 COLLATE latin1_german2_ci;

If the default collation is not set in the database config (e.g. in my.cnf as default-collation) or during the database creation, the MySQL default is taken, which is latin1_swedish_ci. This can be verified with the following command:

mysql> show collation where Charset = 'latin1';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+
8 rows in set (0.00 sec)

This could result in the following problem: The database itself has a collation of latin1_swedish_ci while the tables run with collation latin1_german2_ci. If stored procedures or triggers come into the database, they're stored as latin1_swedish_ci but try to handle data from the tables, which are latin1_german2_ci. This (could) results in an error. To solve this, the database collation has to be changed.

First, let's get the current database collation:

mysql> use test;
Database changed
mysql> SHOW VARIABLES LIKE '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_german2_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Now let's change (ALTER) the database collation:

mysql> ALTER DATABASE test DEFAULT CHARACTER SET = latin1 DEFAULT COLLATE = latin1_german2_ci;

... and verify the collation variables again:

mysql> use test;
Database changed
mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_german2_ci |
| collation_server     | latin1_german2_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Success! After deleting and re-creating the stored function, it is now working correctly.