##1. MySQL will appear The reason for garbled Chinese charactersWhen we use the MySQL database, we often encounter the problem of garbled characters. See the following code.
mysql> create table test(id int,name varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into test values(1,'宋蔚然'); ERROR 1366 (HY000): Incorrect string value: '\xE5\xAE\x8B\xE8\x94\x9A...' for column 'name' at row 1 mysql>
Related learning recommendations:Obviously, when inserting Chinese, an error is reported. What is the reason?
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_set_client The character encoding used by the client
character_set_connection The encoding used by the database link
character_set_database The character encoding used by the database
It turns out that the character encoding is not unified with the encoding of the server and the database. of.
2. The solution to Chinese garbled characters appearing 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(1,'宋蔚然'); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +------+-----------+ | id | name | +------+-----------+ | 1 | 宋蔚然 | +------+-----------+ 1 row in set (0.00 sec)Come again Take a look at the character set settings
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 encoding is unified, there will be no garbled characters.
Solving the problem of garbled characters is actually to unify the character encoding of the client with the encoding of the server and database. The server and database encodings here are latin1, and all set names latin1 can temporarily solve the garbled problem.
Method 2: Modify the database configuration file character set to UTF8UTF8 supports many language systems, so it is strongly recommended to set the character encoding to UTF8 in production. Open the database configuration file and add the following content 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_ciRestart the database
[root@test ~]# systemctl restart mysqldrebuilding the creation library and table
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(1,'宋蔚然'); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +------+-----------+ | id | name | +------+-----------+ | 1 | 宋蔚然 | +------+-----------+ 1 row in set (0.00 sec)again to 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/ | +--------------------------+----------------------------+
#1. Reasons why Chinese garbled characters appear in MySQL
When we use the MySQL database, we often encounter garbled characters. Question, look at the code below.
mysql> create table test(id int,name varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into test values(1,'宋蔚然'); ERROR 1366 (HY000): Incorrect string value: '\xE5\xAE\x8B\xE8\x94\x9A...' for column 'name' at row 1 mysql>
Obviously, an error is reported when inserting Chinese characters. What is the reason?
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_set_client The character encoding used by the client
character_set_connection The encoding used by the database link
character_set_database The character encoding used by the database
It turns out that the character encoding is not unified with the encoding of the server and the database. of.
2. The solution to Chinese garbled characters appearing 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(1,'宋蔚然'); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +------+-----------+ | id | name | +------+-----------+ | 1 | 宋蔚然 | +------+-----------+ 1 row in set (0.00 sec)
Come again Take a look at the character set settings
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 encoding is unified, there will be no garbled characters.
Solving the problem of garbled characters is actually to unify the character encoding of the client with the encoding of the server and database. The server and database encodings here are latin1, and setting all 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 database configuration file and add the following content 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
Rewrite the created library and table
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(1,'宋蔚然'); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +------+-----------+ | id | name | +------+-----------+ | 1 | 宋蔚然 | +------+-----------+ 1 row in set (0.00 sec)
Let’s take a look at the character set settings
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/ | +--------------------------+----------------------------+
The above is the detailed content of How to solve the problem of Chinese garbled characters in MySQL. For more information, please follow other related articles on the PHP Chinese website!