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

user_229656 0
Viewed 576468

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?

character-encoding mysql
19 Answers

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
user_19300230

enter image description here

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.

user_6245330
Related