Cyrillic encoding in MySQL-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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