绵阳梓潼网站制作公司

解决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: '\\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/ |
    +--------------------------+----------------------------+

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会出现中文乱码问题的方法
文章URL：http://myzitong.com/article/cjjhhi.html

其他资讯