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

Mysql Problem Overview

Currently we are using the following commands in PHP to set the character set to UTF-8 in our application.

Since this is a bit of overhead, we'd like to set this as the default setting in MySQL. Can we do this in /etc/my.cnf or in another location?

SET NAMES 'utf8'
SET CHARACTER SET utf8

I've looked for a default charset in /etc/my.cnf, but there's nothing there about charsets.

At this point, I did the following to set the MySQL charset and collation variables to UTF-8:

skip-character-set-client-handshake
character_set_client=utf8
character_set_server=utf8

Is that a correct way to handle this?

Mysql Solutions

Solution 1 - Mysql

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 - Mysql

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 - Mysql

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 - Mysql

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)

Solution 5 - Mysql

Note: my.cnf file is located at /etc/mysql/

After adding these lines:

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

[client]
default-character-set   = utf8

[mysql]
default-character-set   = utf8

Don't forget to restart server:

sudo service mysql restart

Solution 6 - Mysql

MySQL v5.5.3 and greater:

Just add three lines only in the [mysqld] section:

[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake

Note: Including skip-character-set-client-handshake here obviates the need to include both init-connect in [mysqld] and default-character-set in the [client] and [mysql] sections.

Solution 7 - Mysql

NijaCat was close, but specified overkill:

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

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

Then, to verify:

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_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Solution 8 - Mysql

I also have found out that after setting default-character-set = utf8 under [mysqld] title, MySQL 5.5.x would not start under Ubuntu 12.04 (Precise Pangolin).

Solution 9 - Mysql

All settings listed here are correct, but here are the most optimal and sufficient solution:

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
character-set-server = utf8
collation-server = utf8_unicode_ci

[client]
default-character-set = utf8

Add these to /etc/mysql/my.cnf.

Please note, I choose utf8_unicode_ci type of collation due to the performance issue.

The result is:

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

And this is when you connect as non-SUPER user!

For example, the difference between connection as SUPER and non-SUPER user (of course in case of utf8_unicode_ci collation):

user with SUPER priv.:

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci | <---
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

user with non-SUPER priv.:

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

I wrote a comprehensive article (rus) explaining in details why you should use one or the other option. All types of Character Sets and Collations are considered: for server, for database, for connection, for table and even for column.

I hope this and the article will help to clarify unclear moments.

Solution 10 - Mysql

Under Xubuntu 12.04 I simply added

[mysqld]
character_set_server = utf8

to /etc/mysql/my.cnf

And the 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_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Also take a look at http://dev.mysql.com/doc/refman/5.6/en/charset-server.html

Solution 11 - Mysql

The directive has changed to character-set-system=utf8

http://dev.mysql.com/doc/refman/5.6/en/charset-configuration.html

Solution 12 - Mysql

On Fedora 21

$ vi /etc/my.cnf

Add follow:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[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

Save and exit.

Final remember restart service mysqld with service mysqld restart.

Solution 13 - Mysql

MySQL versions and Linux distributions may matter when making configurations.

However, the changes under [mysqld] section is encouraged.

I want to give a short explanation of tomazzlender's answer:

[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

[mysqld]

This will change collation_connection to utf8_unicode_ci

init_connect='SET collation_connection = utf8_unicode_ci'

Using SET NAMES:

init_connect='SET NAMES utf8'

The SET NAMES will influence three characters, that is:

character_set_client
character_set_results
character_set_connection

This will set character_set_database & character_set_server

character-set-server=utf8

This will only affect collation_database & collation_server

collation-server=utf8_unicode_ci

Sorry, I'm not so sure what is this for. I don't use it however:

skip-character-set-client-handshake

Solution 14 - Mysql

MySQL 5.5, all you need is:

[mysqld]
character_set_client=utf8
character_set_server=utf8
collation_server=utf8_unicode_ci

collation_server is optional.

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| 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)

Solution 15 - Mysql

If you're having trouble confirming the client's character-set support using MySQL Workbench, then keep the following note in mind:

Important All connections opened by MySQL Workbench automatically set the client character set to utf8. Manually changing the client character set, such as using SET NAMES ..., may cause MySQL Workbench to not correctly display the characters. For additional information about client character sets, see Connection Character Sets and Collations.

Thus I was unable to override MySQL Workbench's character sets with my.cnf changes. e.g. 'set names utf8mb4'

Solution 16 - Mysql

If you are confused by your setting for client and conn is reseted after restart mysql service. Try these steps (which worked for me):

vi /etc/my.cnf
add the contents blow and :wq [client] character-sets-dir=/usr/local/mysql/share/mysql/charsets
restart mysql and login mysql , use database, input command status;, you'll find the character-set for 'client' and 'conn' is set to 'utf8'.

Check the reference for more info.

Solution 17 - Mysql

For utf8mb4_general_ci

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4
    
[mysqld]
collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

As per symfony framework Documentation at https://symfony.com/doc/2.6/book/doctrine.html#configuring-the-database

> We recommend against MySQL’s utf8 character set, since it does not > support 4-byte unicode characters, and strings containing them will be > truncated. This is fixed by the newer utf8mb4 character set.

Solution 18 - Mysql

You can do it the way it does, and if it doesn't work, you need to restart mysql.

Solution 19 - Mysql

Change MySQL character:

Client

default-character-set=utf8

mysqld

character_set_server=utf8

We should not write default-character-set=utf8 in mysqld, because that could result in an error like:

> start: Job failed to start

At last:

 +--------------------------+----------------------------+
 | 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/ |
 +--------------------------+----------------------------+

Content Type	Original Author	Original Content on Stackoverflow
Question	Jorre	View Question on Stackoverflow
Solution 1 - Mysql	NinjaCat	View Answer on Stackoverflow
Solution 2 - Mysql	Mustafah	View Answer on Stackoverflow
Solution 3 - Mysql	basic6	View Answer on Stackoverflow
Solution 4 - Mysql	tomazzlender	View Answer on Stackoverflow
Solution 5 - Mysql	М.Б.	View Answer on Stackoverflow
Solution 6 - Mysql	mug896	View Answer on Stackoverflow
Solution 7 - Mysql	Derek	View Answer on Stackoverflow
Solution 8 - Mysql	amiroff	View Answer on Stackoverflow
Solution 9 - Mysql	Sergei Danielian	View Answer on Stackoverflow
Solution 10 - Mysql	Falko	View Answer on Stackoverflow
Solution 11 - Mysql	Loren	View Answer on Stackoverflow
Solution 12 - Mysql	Nguyễn Văn Tiến	View Answer on Stackoverflow
Solution 13 - Mysql	Lihang Li	View Answer on Stackoverflow
Solution 14 - Mysql	Gajus	View Answer on Stackoverflow
Solution 15 - Mysql	paiego	View Answer on Stackoverflow
Solution 16 - Mysql	John Wen	View Answer on Stackoverflow
Solution 17 - Mysql	Sudhakar Krishnan	View Answer on Stackoverflow
Solution 18 - Mysql	郭浩伟	View Answer on Stackoverflow
Solution 19 - Mysql	MYLOGOS	View Answer on Stackoverflow