Cyrillic encoding in MySQL

Question

In my.ini I've changed properties from latin1 to cp1251 (then restarted the server)

[mysql]
default-character-set=cp1251

............................

[mysqld]
default-character-set=cp1251

I create database

CREATE DATABASE library DEFAULT CHARSET=cp1251;

Make request to check out the encoding:

SELECT @@character_set_database, @@collation_database;

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| cp1251                   | cp1251_general_ci    |
+--------------------------+----------------------+

show variables like "char%";

+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |

    +--------------------------+---------------------------------------------------------+
    | character_set_client     | cp1251                                                  |
    | character_set_connection | cp1251                                                  |
    | character_set_database   | cp1251                                                  |
    | character_set_filesystem | binary                                                  |
    | character_set_results    | cp1251                                                  |
    | character_set_server     | cp1251                                                  |
    | character_set_system     | utf8                                                    |
    | character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\ |
    +--------------------------+---------------------------------------------------------+

Create a table

CREATE TABLE genres (g_id INT, g_name VARCHAR(150)) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

As I try to insert cyrillic data, the Command Line window gets stuck:

mysql> INSERT INTO genres (g_id, g_name) VALUES (1, 'Поэзия');
    '>
    '>
    '>
    '>

Latin strings get inserted ok:

mysql> INSERT INTO genres (g_id, g_name) VALUES (1, 'Poetry');
Query OK, 1 row affected (0.06 sec)

Yesterday, after the whole day of trying and testing, I got it working well. Created some more tables and inserted some Cyrillic strings. But next morning and the whole day long I can't get it working again. The previously inserted data wouldn't display. After firing

set names utf8

the Cyrillic words appeared, numeric columns didn't show right. What have I missed?

[UTF-8 Everywhere](https://utf8everywhere.org/) and [UTF-8 all the way through](https://stackoverflow.com/questions/279170/). — JosefZ, Apr 30 '22 at 07:47

score 0 · Answer 1 · answered Apr 29 '22 at 23:07

It's not just one change.

character_set_client/connection/results, but not the other two that you changed, specify the encoding of the client.
The column definitions in the database tables need to have a character set that can handle Cyrillic. One way is to do this to each table:
```
ALTER TABLE t CONVERT TO cp1251;
```
Have you have already stored Cyrillic in latin1 columns? Check by doing SELECT HEX(col) .... You may need the 2-step Alter as discussed in http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases
It would be best to switch to utf8mb4; that way you could handle all character sets throughout the world.

See also Trouble with UTF-8 characters; what I see is not what I stored

score 0 · Answer 2 · answered Apr 30 '22 at 16:36

I have found a workaround. After starting cmd

C:\Users\nikol>chcp 866
Active code page: 866

Then after starting mysql

mysql> set names cp866;
Query OK, 0 rows affected (0.00 sec)

But when I select the data, there are multiple trailing spaces

mysql> SELECT * FROM genres;
+------+------------------+
| g_id | g_name           |
+------+------------------+
|    1 | Поэзия                |
|    2 | Программирование              |
|    3 | Психология               |
|    4 | Наука               |
|    5 | Классика                |
|    6 | Фантастика                |
+------+------------------+
6 rows in set (0.00 sec)

I guess I'll have to TRIM

It works fine if in cmd fire 'chcp 65001' and in mysql 'set names utf8;' — Николай Беляков, Apr 30 '22 at 16:50

Cyrillic encoding in MySQL

2 Answers2