解决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会涌现中文乱码题目的办法的细致内容,更多请关注 百分百源码网 其它相干文章!