Shulou(Shulou.com)06/01 Report--

Editor to share with you what to do about the problem of Chinese garbled code in MySQL. I hope you will gain a lot after reading this article. Let's discuss it together.

1. Reasons for Chinese garbled codes in MySQL

When we are using MySQL database, we often encounter the problem of garbled code. Take a look at the following code.

Mysql > create table test (id int,name varchar (10)); Query OK, 0 rows affected (0.01sec) mysql > insert into test values (1Med 'Song Weiran'); ERROR 1366 (HY000): Incorrect string value:'\ xE5\ xAE\ x8B\ xE8\ x94\ x9A..' For column 'name' at row 1 mysql >

Recommended for related study: mysql video tutorial

Obviously, what is the reason for reporting an error when inserting Chinese?

Mysql > show variables like'% CHARACTER%' +-- +-- + | Variable_name | Value | +-- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/share/mysql/charsets/ | +-+-+ |

Character encoding used by character_set_client clients

Encoding used by character_set_connection database links

Character encodings used by character_set_database databases

It turns out that it is caused by the disunity of character coding, server coding and database coding.

2. The solution to Chinese garbled code in MySQL

Method 1: set names

Mysql > set names latin1; mysql > set names latin1; Query OK, 0 rows affected (0.00 sec) mysql > select * from test; Empty set (0.00 sec) mysql > insert into test values; Query OK, 1 row affected (0.01 sec) mysql > select * from test +-+-+ | id | name | +-+-+ | 1 | Song Weiran | +-+-+ 1 row in set (0.00 sec)

Let's take a look at the settings of the character set.

Mysql > show variables like'% CHARACTER%' +-- +-- + | Variable_name | Value | +-- -+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/share/mysql/charsets/ | +-+-+ |

If the character coding is uniform, there will be no garbled code.

To solve the problem of garbled code is to unify the character coding of the client with the coding of the server and the database. The server and database codes here are latin1, and all set names latin1 can temporarily solve the garbled problem.

Method 2: modify the database configuration file character set to UTF8

UTF8 supports many language systems, so it is strongly recommended to set the character encoding to UTF8 in production. Open the configuration file of the database and add the following under [client], [mysql] and [mysqld], respectively.

# vi / mysql/data/3306/my.cnf [client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci

Restart the database

[root@test ~] # systemctl restart mysqld

Override creating libraries and tables

Mysql > create database test; Query OK, 1 row affected (0.00 sec) mysql > use test; Database changed mysql > create table test (id int,name varchar (10)); Query OK, 0 rows affected (0.02 sec) mysql > insert into test values; Query OK, 1 row affected (0.00 sec) mysql > select * from test +-+-+ | id | name | +-+-+ | 1 | Song Weiran | +-+-+ 1 row in set (0.00 sec)

Let's take a look at the settings of the character set.

Mysql > show variables like'% CHARACTER%' +-- +-- + | Variable_name | Value | +-- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | / usr/share/mysql/charsets/ | +-| -+

Undefined

1. Reasons for Chinese garbled codes in MySQL

When we are using MySQL database, we often encounter the problem of garbled code. Take a look at the following code.

Mysql > create table test (id int,name varchar (10)); Query OK, 0 rows affected (0.01sec) mysql > insert into test values (1Med 'Song Weiran'); ERROR 1366 (HY000): Incorrect string value:'\ xE5\ xAE\ x8B\ xE8\ x94\ x9A..' For column 'name' at row 1 mysql >

Obviously, what is the reason for reporting an error when inserting Chinese?

Mysql > show variables like'% CHARACTER%' +-- +-- + | Variable_name | Value | +-- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/share/mysql/charsets/ | +-+-+ |

Character encoding used by character_set_client clients

Encoding used by character_set_connection database links

Character encodings used by character_set_database databases

It turns out that it is caused by the disunity of character coding, server coding and database coding.

2. The solution to Chinese garbled code in MySQL

Method 1: set names

Mysql > set names latin1; mysql > set names latin1; Query OK, 0 rows affected (0.00 sec) mysql > select * from test; Empty set (0.00 sec) mysql > insert into test values; Query OK, 1 row affected (0.01 sec) mysql > select * from test +-+-+ | id | name | +-+-+ | 1 | Song Weiran | +-+-+ 1 row in set (0.00 sec)

Let's take a look at the settings of the character set.

Mysql > show variables like'% CHARACTER%' +-- +-- + | Variable_name | Value | +-- -+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | / usr/share/mysql/charsets/ | +-+-+ |

If the character coding is uniform, there will be no garbled code.

To solve the problem of garbled code is to unify the character coding of the client with the coding of the server and the database. The server and database codes here are latin1, and all set names latin1 can temporarily solve the garbled problem.

Method 2: modify the database configuration file character set to UTF8

UTF8 supports many language systems, so it is strongly recommended to set the character encoding to UTF8 in production. Open the configuration file of the database and add the following under [client], [mysql] and [mysqld], respectively.

# vi / mysql/data/3306/my.cnf [client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci

Restart the database

[root@test ~] # systemctl restart mysqld

Override creating libraries and tables

Mysql > create database test; Query OK, 1 row affected (0.00 sec) mysql > use test; Database changed mysql > create table test (id int,name varchar (10)); Query OK, 0 rows affected (0.02 sec) mysql > insert into test values; Query OK, 1 row affected (0.00 sec) mysql > select * from test +-+-+ | id | name | +-+-+ | 1 | Song Weiran | +-+-+ 1 row in set (0.00 sec)

Let's take a look at the settings of the character set.

Mysql > show variables like'% CHARACTER%' +-- +-- + | Variable_name | Value | +-- -+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | / usr/share/mysql/charsets/ | +-| -+ after reading this article I believe you have a certain understanding of what to do about the Chinese garbled problem in MySQL. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.