Change MySQL default character set to UTF-8 in my.cnf?

Solution 1:

To set the default to UTF-8, you want to add the following to my.cnf/my.ini

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4


[mysqld]
collation-server = utf8mb4_unicode_520_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

If you want to change the character set for an existing DB, let me know... your question didn't specify it directly so I am not sure if that's what you want to do.

Edit: I replaced utf8 with utf8mb4 in the original answer due to utf8 only being a subset of UTF-8. MySQL and MariaDB both call UTF-8 utf8mb4.

Solution 2:

For the recent version of MySQL,

default-character-set = utf8

causes a problem. It's deprecated I think.

As Justin Ball says in "Upgrade to MySQL 5.5.12 and now MySQL won’t start, you should:

Remove that directive and you should be good.

Then your configuration file ('/etc/my.cnf' for example) should look like that:

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Restart MySQL.

For making sure, your MySQL is UTF-8, run the following queries in your MySQL prompt:

First query:

 mysql> show variables like 'char%';

The output should look like:

 +--------------------------+---------------------------------+
 | Variable_name            | Value                           |
 +--------------------------+---------------------------------+
 | character_set_client     | utf8                            |
 | character_set_connection | utf8                            |
 | character_set_database   | utf8                            |
 | character_set_filesystem | binary                          |
 | character_set_results    | utf8                            |
 | character_set_server     | utf8                            |
 | character_set_system     | utf8                            |
 | character_sets_dir       | /usr/local/mysql/share/charsets/|
 +--------------------------+---------------------------------+

Second query:

 mysql> show variables like 'collation%';

And the query output is:

 +----------------------+-----------------+
 | Variable_name        | Value           |
 +----------------------+-----------------+
 | collation_connection | utf8_general_ci |
 | collation_database   | utf8_unicode_ci |
 | collation_server     | utf8_unicode_ci |
 +----------------------+-----------------+

Solution 3:

This question already has a lot of answers, but Mathias Bynens mentioned that 'utf8mb4' should be used instead of 'utf8' in order to have better UTF-8 support ('utf8' does not support 4 byte characters, fields are truncated on insert). I consider this to be an important difference. So here is yet another answer on how to set the default character set and collation. One that'll allow you to insert a pile of poo (💩).

This works on MySQL 5.5.35.

Note, that some of the settings may be optional. As I'm not entirely sure that I haven't forgotten anything, I'll make this answer a community wiki.

Old Settings

mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Config

# 💩 𝌆
# UTF-8 should be used instead of Latin1. Obviously.
# NOTE "utf8" in MySQL is NOT full UTF-8: http://mathiasbynens.be/notes/mysql-utf8mb4

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

New Settings

mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| 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)

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

character_set_system is always utf8.

This won't affect existing tables, it's just the default setting (used for new tables). The following ALTER code can be used to convert an existing table (without the dump-restore workaround):

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Edit:

On a MySQL 5.0 server: character_set_client, character_set_connection, character_set_results, collation_connection remain at latin1. Issuing SET NAMES utf8 (utf8mb4 not available in that version) sets those to utf8 as well.

Caveat: If you had a utf8 table with an index column of type VARCHAR(255), it can't be converted in some cases, because the maximum key length is exceeded (Specified key was too long; max key length is 767 bytes.). If possible, reduce the column size from 255 to 191 (because 191 * 4 = 764 < 767 < 192 * 4 = 768). After that, the table can be converted.

Solution 4:

On MySQL 5.5 I have in my.cnf

[mysqld] 
init_connect='SET collation_connection = utf8_unicode_ci' 
init_connect='SET NAMES utf8' 
character-set-server=utf8 
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake

Result is

mysql> show variables like "%character%";show variables like "%collation%";

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)