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

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

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.

Answer 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:

1. Remove that directive and you should be good.

2. 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 

3. Restart MySQL.

4. 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 |  +----------------------+-----------------+