Additional languages
MySQL 3.23, 4.0, 4.1 Reference Manual :: 10 Character Set Support :: 10.4 Connection Character Sets and Collations
« 10.3.8 Compatibility with Other DBMSs
10.5 Collation Issues »
Section Navigation      [Toggle]

10.4. Connection Character Sets and Collations

Several character set and collation system variables relate to a client's interaction with the server. Some of these have been mentioned in earlier sections:

  • The server character set and collation can be determined from the values of the character_set_server and collation_server system variables.

  • The character set and collation of the default database can be determined from the values of the character_set_database and collation_database system variables.

Additional character set and collation system variables are involved in handling traffic for the connection between a client and the server. Every client has connection-related character set and collation system variables.

Consider what a “connection” is: It's what you make when you connect to the server. The client sends SQL statements, such as queries, over the connection to the server. The server sends responses, such as result sets, over the connection back to the client. This leads to several questions about character set and collation handling for client connections, each of which can be answered in terms of system variables:

  • What character set is the statement in when it leaves the client?

    The server takes the character_set_client system variable to be the character set in which statements are sent by the client.

  • What character set should the server translate a statement to after receiving it?

    For this, the server uses the character_set_connection and collation_connection system variables. It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.

  • What character set should the server translate to before shipping result sets or error messages back to the client?

    The character_set_results system variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, and result metadata such as column names.

You can fine-tune the settings for these variables, or you can depend on the defaults (in which case, you can skip the rest of this section).

There are two statements that affect the connection character sets: 

SET NAMES 'charset_name'
SET CHARACTER SET charset_name

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'cp1251' tells the server “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)

A SET NAMES 'x' statement is equivalent to these three statements: 

SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

Setting character_set_connection to x also sets collation_connection to the default collation for x. It is not necessary to set that collation explicitly. To specify a particular collation for the character sets, use the optional COLLATE clause: 

SET NAMES 'charset_name' COLLATE 'collation_name'

SET CHARACTER SET is similar to SET NAMES but sets character_set_connection and collation_connection to character_set_database and collation_database. A SET CHARACTER SET x statement is equivalent to these three statements: 

SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database;

Setting collation_connection also sets character_set_connection to the character set associated with the collation (equivalent to executing SET character_set_connection = @@character_set_database). It is not necessary to set character_set_connection explicitly.

When a client connects, it sends to the server the name of the character set that it wants to use. The server uses the name to set the character_set_client, character_set_results, and character_set_connection system variables. In effect, the server performs a SET NAMES operation using the character set name.

With the mysql client, it is not necessary to execute SET NAMES every time you start up if you want to use a character set different from the default. You can add the --default-character-set option setting to your mysql statement line, or in your option file. For example, the following option file setting changes the three character set variables set to koi8r each time you invoke mysql: 

[mysql]
default-character-set=koi8r

Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET latin1 before issuing the SELECT statement, the server converts the latin2 values to latin1 just before sending results back. Conversion may be lossy if there are characters that are not in both character sets.

If you do not want the server to perform any conversion of result sets, set character_set_results to NULL: 

SET character_set_results = NULL;

Note: Currently, UCS-2 cannot be used as a client character set, which means that SET NAMES 'ucs2' does not work.

To see the values of the character set and collation system variables that apply to your connection, use these statements: 

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

User Comments

Posted by Peter Didenko on April 23 2004 2:58am[Delete] [Edit]

This example are usable for russian users who want to have windows-1251 encoding on the site and koi8-r encoding into the database:

set CHARACTER SET cp1251_koi8

Posted by N Bernhardt on August 17 2005 2:51pm[Delete] [Edit]

If you are wondering why -despite all UTF8 settings- you still don't get non-ASCII characters right, it might be the case that the _connection_ character set is still standard latin1.

To change the connection charset permanently to UTF-8, add the following line in the [mysqld] section:
[mysqld]
init-connect='SET NAMES utf8'

The other way to let MySQL know what connection charset you intend to use is per-connection based. After a connection is established (with host, name, password), add the following two lines in your application:
SET NAMES utf8;
SET CHARACTER_SET utf8;

The last hint is given most of the time, but not everybody is happy to change every application (esp. when some lazy add-on and extension programmers use their own connection stuff instead of the (PHP) application.

Posted by Roger Wu on September 29 2005 11:41pm[Delete] [Edit]

mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;

When a client connects, it sends to the server the name of the character set that it wants to use. The server sets the character_set_client, character_set_results, and character_set_connection variables to that character set. (In effect, the server performs a SET NAMES operation using the character set.)

With the mysql client, it is not necessary to execute SET NAMES every time you start up if you want to use a character set different from the default. You can add the --default-character-set option setting to your mysql statement line, or in your option file. For example, the following option file setting changes the three character set variables set to koi8r each time you run mysql:

[mysql]
default-character-set=koi8r

Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET latin1, then just before sending results back, the server converts the latin2 values to latin1. Conversion may be lossy if there are characters that are not in both character sets.

If you do not want the server to perform any conversion, set character_set_results to NULL:

mysql> SET character_set_results = NULL;

Posted by Hendri Hondorp on September 7 2006 1:11pm[Delete] [Edit]

If you are wondering why -despite all UTF8 settings- you still don't get non-ASCII characters right, it might be the case that:
1. you are using mysqlimport to insert data from files with utf8 characters;
AND
2. you have created a Database with character set latin1 (this is the default!) and not with character set utf8.
3. You have created a Table with character set utf8.

Even if you use the --default-character-set=utf8 option for mysqlimport this doesn't work! Mysqlimport is only using the value of 'character_set_database' as character set and in this case it is 'latin1'.

Posted by [name withheld] on September 17 2006 11:09am[Delete] [Edit]

PHP/mysql connections seem to be made by default in latin1, so if you are experiencing problems with characters just set everything you can to UTF-8 (collations, charsets, html page encoding) and just after connecting to da database send the following sql query to Mysql:

SET NAMES 'utf8'

This should be done once every time you connect to mysql.


Posted by Yuri Tsarev on October 4 2006 2:01am[Delete] [Edit]

This example shows how to configure mysqld server to use pure utf8 for server's character set and collation instead of default latin1. This will help to correctly store non-latin character data in db (cyrillic сharacters for example and russian language in particularly).

Go to the [mysqld] section in my.cnf and add two strings:

collation_server=utf8_unicode_ci
character_set_server=utf8

You can also add

skip-character-set-client-handshake

to enforce using of utf8 encoding in db.


Posted by Davit Tutberidze on December 19 2006 1:13pm[Delete] [Edit]

Recently I have encountered the same problems mentioned in comments regarding UTF-8 collation and proper appearance of symbols in a webpage (specifically in Georgian and Russian). The very key to my troubles turned up the following: regardless all the efforts to set some default parameters, you still need to send the query SET NAMES 'utf-8' to the server every time after you select a database and before you select from the table:

mysql_query("SET NAMES 'utf-8'");

The thing is that you set character_set_client and character_set_results to utf-8 by executing this query. In addition, you have to deal with some little tasks of defining utf-8 collations and charsets inside your database that are clearly explained in comments above.

Posted by Roy Hiu-yeung Chan on January 23 2007 12:47pm[Delete] [Edit]

If you think "set names utf8" for each connection is too trouble, you can modify my.cnf to solve the problem forever. In my.cnf, add the line "default-character-set=utf8" in both [mysqld] and [client] sections:

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8

The mysql will use utf8 after you restart it.

Posted by ggw m on February 2 2007 7:15pm[Delete] [Edit]

Make sure to type "utf8", not "utf-8":

>>
$link1=mysql_connect($host, $user, $PW)
or die ('DB yok');
mysql_select_db($db, $link1)
or die ('table yok');
mysql_query("SET NAMES utf8");
<<
(the only way if you have no rights to change the config)

Posted by Bruno Longo on February 8 2007 1:54pm[Delete] [Edit]

I tried all the above hints but still couldn´t get it to work (using mySql and PHP 5 server)... ´till I found and tried also this:

add to the .htaccess file this single line:

AddDefaultCharset UTF-8

Still not sure why this was important to the whole, but now it works as planned! Thanks!

Posted by Charly R on March 29 2007 12:24am[Delete] [Edit]

I've spent quite a lot of time trying to make MySQL 4.1 and PHP working with my tables in cp1251 charset.

Adding on my local PC the following line to the my.ini solved the problem but unfortunately I can't do the same on my hosting provider space
default-character-set=cp1251

So after trying quite a lot of combinations finally I've found that I need to add only one query set just after connecting to the database in my php script
mysql_query ('SET NAMES CP1251');

Please note if you run another query like
mysql_query ('SET CHARACTER SET CP1251');
just after first one it will reset character_set_connection option and all your symbols most likely will be converted to the question marks as it happens for me many times before.

Add your own comment.