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

Question

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?

Answer 1

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.

Answer 2

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

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

Answer 3

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

Answer 4

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 (

查看更多

0人赞 添加讨论(0) 举报

Answer 5

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.

Answer 6

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)