MySQL会出现中文乱码问题怎么办
小编给大家分享一下MySQL会出现中文乱码问题怎么办，希望大家阅读完这篇文章后大所收获，下面让我们一起去探讨吧！
成都创新互联公司专注于企业全网整合营销推广、网站重做改版、轵城网站定制设计、自适应品牌网站建设、H5页面制作、商城网站建设、集团公司官网建设、成都外贸网站制作、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为轵城等各大城市提供网站开发制作服务。
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: '\xE5\xAE\x8B\xE8\x94\x9A...' for column 'name' at row 1
mysql>相关学习推荐：mysql视频教程
很显然，当插入中文的时候报错，原因在哪呢？
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会出现中文乱码的解决方法
方法一：设置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)再来看一下字符集的设置
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即可临时解决乱码问题。
方法二：修改数据库配置文件字符集为UTF8
UTF8支持很多语言体系，因此在生产中强烈建议将字符编码设置UTF8。打开数据库的配置文件，分别在[client]，[mysql]，[mysqld]下添加如下内容。
#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重启数据库
[root@test ~]# systemctl restart mysqld
重写创建库和表
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)再来看一下字符集的设置
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、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: '\xE5\xAE\x8B\xE8\x94\x9A...' for column 'name' at row 1
mysql>很显然，当插入中文的时候报错，原因在哪呢？
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会出现中文乱码的解决方法
方法一：设置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)再来看一下字符集的设置
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即可临时解决乱码问题。
方法二：修改数据库配置文件字符集为UTF8
UTF8支持很多语言体系，因此在生产中强烈建议将字符编码设置UTF8。打开数据库的配置文件，分别在[client]，[mysql]，[mysqld]下添加如下内容。
#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重启数据库
[root@test ~]# systemctl restart mysqld
重写创建库和表
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)再来看一下字符集的设置
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会出现中文乱码问题怎么办有了一定的了解，想了解更多相关知识，欢迎关注创新互联行业资讯频道，感谢各位的阅读！
分享文章：MySQL会出现中文乱码问题怎么办
URL地址：http://scyanting.com/article/jggsis.html
