CODEWITHSUNDEEP
mysqlcharacter-encoding
Leo Jiang
Leo Jiang

Reputation: 26223

character-set-server VS default-character-set in MySQL

What's the difference between character-set-server and default-character-set in my.cnf? I want to set MySQL's connection to UTF8 and both of these seem to work. Is one better than the other?

Upvotes: 4

Views: 3010

Answers (2)

newtover
newtover

Reputation: 32094

Here is a quote from MySQL docs:

You can force client programs to use specific character set as follows:

[client]
default-character-set=charset_name

This is normally unnecessary. However, when character_set_system differs from character_set_server or character_set_client, and you input characters manually (as database object identifiers, column values, or both), these may be displayed incorrectly in output from the client or the output itself may be formatted incorrectly. In such cases, starting the mysql client with --default-character-set=system_character_set—that is, setting the client character set to match the system character set—should fix the problem.

In other words, character_set_server and character_set_client are settings for mysqld, when default-character-set is a setting for mysql and other client libraries which overrides character_set_client assumed by mysqld by default.

You may not see the difference if you connect with mysql to localhost, but default-character-set is used as well when you connect to some other server, which may have other defaults.

UPD from 2018-08-17

As John Smith noticed, my answer is currently outdated, but the essence of it is still correct: character_set_server is a server variable, but when you connect to the mysqld with a client, you should specify the client and connection settings.

In these days much many computers used as clients and servers have utf-8 as a default for locale encoding and only because of that setting character-set-server instead of default-character-set might seem to work.

To be clear, to set up mysqld (that is server) to use utf-8 and some its collation as default for schema names, table names, column names and column values instead of latin1_swedish_ci you should set up characted-set-server in mysqld configuration.

But when you connect a mysql client to the server, your current charset may be any other, and to correctly convert to the server character set the data you send over a connection and to convert back the data sent from the server as response, you should specify the corresponding client settings:

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

or the corresponding settings in mysql.ini for your client application. If all of them are the same, you can start your communication with server with a shorter statement:

SET NAMES 'charset_name' [COLLATE 'collation_name'];

Upvotes: 0

John Smith
John Smith

Reputation: 1099

Always use

character-set-server

The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose (source)

character-set-server has replaced default-character-set setting, since default-character-set is now deprecated and can cause problems.

p.s. I believe the answer by newtover is wrong.

Upvotes: 1

Related Questions