+7 (495) 668-09-95

Knowledge base

Encoding in MySQL

MySQL Encoding

Problems with MySQL encoding can occur for versions 4.1 and later because they have the ability to set different encoding for different levels of the database hierarchy (server, database, table, column) and for individual connections between the server and the client. MySQL has latin1 default coding at all levels.

The encoding, including the data on the MySQL server, should match the encoding of the data itself. For example, Russian characters use cp1251 coding. If the table includes entries in Russian characters, the encoding of the table must be set as cp1251, otherwise question marks or other symbols will be displayed instead of Russian characters.

When creating a database, specify the coding for storing characters immediately, otherwise the default value (latin1) will be used. For example, create a database using command:

create database `my-db` default charset cp1251;

The server to client connection encoding specifies the format of the data that will be transferred between the two entities. For example, if the website script uses Russian characters, MySQL must correctly recognize the characters when the website refers to the database in order to run the script correctly. If the connection encoding uses the default latin1, the server will be unable to correctly recognize Russian characters, and the script with throw an error.

Set the desired connection coding immediately after connecting to the MySQL server using the following query:

set names cp1251

There are a number of clients that cannot install the necessary encoding and thus, use their own. In such cases, provide the following line to my.cnf file in [mysqld] section:

set init_connect="set names cp1251"

where cp1251 is the coding needed.

In this case, the server will run the "set names cp1251" command immediately after connecting to the client and will set the encoding specified in the query.

The most common Russian encodings are as follows:

utf8, cp866 (DOS), cp1251 (Windows), koi8r

Installation and use of the same encoding for database servers at all levels helps eliminate 90% of problems with the encoding in MySQL.

How to add support for Russian language to MySQL?

Type the following changes to the my.cnf file:

[client]

default-character-set=cp1251

[mysqld]

character-set-server=cp1251

collation-server=cp1251_general_ci

init-connect = "set names cp1251"

Restart the MySQL server.

Return to category