解决MySQL会出现中文乱码问题的方法

1、MySQL会出现中文乱码的原因当我们在使用MySQL数据库的时候，经常会碰到乱码的问题，看下面的代码。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: 'xE5xAEx8BxE8x94x9A...' for column 'name' at row 1mysql>很显然，当插入中文的时候报错，原因在哪呢？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 客户端使用的字符编码character_set_connection 数据库链接使用的编码character_set_database 数据库使用的字符编码原来是字符编码与服务端、数据库的编码不统一而造成的。

2、MySQL会出现中文乱码的解决方法方法一：设置namesmysql> 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)再来看一下字符集的设置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/ |+--------------------------+----------------------------+字符编码统一就不会出现乱码。解决乱码问题其实就是将客户端的字符编码与服务端、数据库的编码统一。这里的服务端与数据库编码都是latin1，所有set names latin1即可临时解决乱码问题。

方法二：修改数据库配置文件字符集为UTF8UTF8支持很多语言体系，因此在生产中强烈建议将字符编码设置UTF8。打开数据库的配置文件，分别在[client]，[mysql]，[mysqld]下添加如下内容。#vi /mysql/data/3306/my.cnf[client]default-character-set=utf8[mysql]default-character-set=utf8[mysqld]default-storage-engine=INNODBcharacter-set-server=utf8collation-server=utf8_general_ci重启数据库[root@test ~]# systemctl restart mysqld重写创建库和表mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql> use test;Database changedmysql> 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)再来看一下字符集的设置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、MySQL会出现中文乱码的原因当我们在使用MySQL数据库的时候，经常会碰到乱码的问题，看下面的代码。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: 'xE5xAEx8BxE8x94x9A...' for column 'name' at row 1mysql>很显然，当插入中文的时候报错，原因在哪呢？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 客户端使用的字符编码character_set_connection 数据库链接使用的编码character_set_database 数据库使用的字符编码原来是字符编码与服务端、数据库的编码不统一而造成的。

2、MySQL会出现中文乱码的解决方法方法一：设置namesmysql> 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)再来看一下字符集的设置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/ |+--------------------------+----------------------------+字符编码统一就不会出现乱码。解决乱码问题其实就是将客户端的字符编码与服务端、数据库的编码统一。这里的服务端与数据库编码都是latin1，所有set names latin1即可临时解决乱码问题。

方法二：修改数据库配置文件字符集为UTF8UTF8支持很多语言体系，因此在生产中强烈建议将字符编码设置UTF8。打开数据库的配置文件，分别在[client]，[mysql]，[mysqld]下添加如下内容。#vi /mysql/data/3306/my.cnf[client]default-character-set=utf8[mysql]default-character-set=utf8[mysqld]default-storage-engine=INNODBcharacter-set-server=utf8collation-server=utf8_general_ci重启数据库[root@test ~]# systemctl restart mysqld重写创建库和表mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql> use test;Database changedmysql> 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)再来看一下字符集的设置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/ |+--------------------------+----------------------------+以上就是解决MySQL会出现中文乱码问题的方法的详细内容，更多请关注小潘博客其它相关文章！