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?

up vote 6 down vote accepted
+50

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.

  • So mysqldump, then dropping and creating a new database is not a good way here? – Alexander Farber Dec 16 '15 at 21:30
  • Dumping and reload would work -- if you get the character sets changed at the right places. mysqldump has a argment; mysql has an argument, etc. Get them wrong an you will a mess. (Sorry, I do not know precisely what to do.) – Rick James Dec 16 '15 at 22:54
  • 1
    To check... latin1 for äöü is hex E4F6FC; utf8/utf8mb4 is C3A4C3B6C3BC. – Rick James Dec 16 '15 at 22:55
  • 1
    For dump+reload you probably would need to either edit the CHARACTER SET specified in the dump or CREATE the tables separately. (Again, I am weak on the details.) – Rick James Dec 16 '15 at 22:56

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.