CODEWITHSUNDEEP
mysql
amorfis
amorfis

Reputation: 15770

Change database variable "character_set_client" in MySQL

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'?

Upvotes: 8

Views: 24130

Answers (2)

photon0
photon0

Reputation: 64

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

Upvotes: 4

dg99
dg99

Reputation: 5663

If you want to change the default for the database (and thus for all users of the database) and you are the administrator of the database, you can either compile it into the mysqld build:

configure --with-charset=latin1

or include it on the command line whenever your server starts/restarts:

mysqld --character-set-server=latin1

See this reference.

If you don't want to change the defaults for the server but you do want to change your default when connecting, then you can use the client command line parameter --default-character-set as described here.

They may also be a way to apply that setting in your .my.cnf file, but I don't know it offhand.

Upvotes: -2

Related Questions