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会出现中文乱码问题的方法的详细内容。