[Solved] MariaDB Add Datas Error: SQL error [1366] [22007]: (cIncorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x89’ for column

MariaDB add datas error:

SQL error [1366] [22007]: (conn=17) Incorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x89’ for column `SAOS`.`user`.`userName` at row 1

SQL:

INSERT into `user` values(1, "ZHANGSAN","123456");

The reason for this is the encoding problem. We can check the database character set encoding:

SHOW VARIABLES LIKE 'character%';

Variable_name                           |Value                       |
————————                +—————————-+
character_set_client                    |utf8mb4                     |
character_set_connection                |utf8mb4                     |
character_set_database                  |latin1                      |
character_set_filesystem                |binary                      |
character_set_results                   |utf8mb4                     |
character_set_server                    |latin1                      |
character_set_system                    |utf8                        |
character_sets_dir                      |/usr/share/mariadb/charsets/|

Above is the result displayed. You can see that there are two latin1 encodings. So to change the encoding, find the my.ini file at

whereis my.ini

The results are as follows:

my: /etc/my. cnf

That means this file replaces my Ini, we modify this file.

Add two lines of configuration:

default-character-set=utf8
character-set-server=utf8

Note that this file will tell you that it is read-only when it is modified. You should empower it:

sudo chmod 777  /etc/my.cnf

Then restart MariaDB.

systemctl restart mariadb.service

You need identity authentication. We can just enter the password.

Then view the database character set encoding:

In this way, insert the data just now, and the result is still an error. It seems that you need to continue to modify the configuration file.

Read More: