Change database variable "character_set_client" in MySQL

user_121595 0
Viewed 16849

I found out in my MySQL I have different global and database "character_set_client" variable. show variables shows 

character_set_client | utf8

while show global variables shows

character_set_client | latin1

I believe the first one is database setting. How can I change it? When I do

set character_set_client='latin1'

it is changed only for session. When I disconnect and connect again it is set back to 'utf8'. How can I change it so that it stays at 'latin1'?

mysql
2 Answers

The idea is to force the character set on the server side and tell it to skip negotiation regarding character set. See MySQL manual(V5.7 at time this was written). Three parameters are vital for correct operation between client and server regarding that matter. They are set in the [mysqld] section of the .cnf file:

[mysqld]
#...    
#UTF8 stuff
skip-character-set-client-handshake
collation-server=latin1_general_ci
character-set-server=latin1

Side note, on this day and age one should always use UTF8, which would require setting the following lines:

collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4

Side-side note, since the release of MySQL 8.0 MySQL manual, the correct values would be:

collation-server=utf8mb4_0900_ai_ci
character-set-server=utf8mb4
user_24258540
Related