MySQL – Convert Database from latin1 to utf8mb4 and Handle German Umlauts

encodingMySQLmysql-5.6utf-8

I am using 5.6.28 MySQL Community Server to host WordPress + ProPhoto blog in German language at CentOS 6.7 Linux server:

mysql> show tables;
+-----------------------+
| Tables_in_blog        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
11 rows in set (0.00 sec)

Then recently I have started developing a Russian language app and had to change MySQL settings to utf8mb4 encoding in /etc/my.cnf:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

Now I have a minor problem with the first database that it is still shown as latin1:

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | latin1                     | HOW TO CONVERT?
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

What is the best way to convert a latin1 database to utf8mb4 please?

And how to ensure that the German umlaut characters ( ä ö ü ) are converted properly in the process?

Best Answer

There are two possible questions here, and they have two different answers --

How do I make all new tables utf8mb4

It can be done (for one database) while creating a database:

CREATE DATABASE dbname
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE       utf8mb4_unicode_ci;

You can ALTER a database (similar syntax), but that only provides a default for future tables.

There are bugs relating to inheriting from the 'server' variables: http://dev.mysql.com/worklog/task/?id=3811 , so I suggest that you simply be explicit on each database.

How do I convert existing latin1 tables

If you have a table declared to be latin1 and correctly contains latin1 bytes, and you would like to change all the char/text columns to utf8...

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

This changes the definition and actively changes the necessary bytes in the columns.

ALTER TABLE tbl MODIFY col1 ... CHARACTER SET utf8mb4;

is similar to the above, but works only one column at a time, and needs exactly the right stuff in the MODIFY clause. Hence, it would be quite tedious. However, it is useful if you want to change only some of the columns.

Related Question