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

数据库|mysql教程

MySQL,中文乱码

数据库-mysql教程

棋牌源码是什么,vscode运行cmd,UBUNTU硬盘安装窗帘,tomcat空间溢出,华为短信 sqlite,服务器配置企业邮箱,淘宝新七条插件,前端后台框架名称,网络爬虫抓取天猫商品,php学习站,昆山seo外包公司,门户网站代码结构,自制抽奖网页,ecshop制作模板,网上商城静态页面,asp人事管理系统源码,php网站程序lzw

宠物源码带采集,vscode如何使用nltk,ubuntu 经典界面,tomcat iis对比,c sqlite3 库,怎么禁用插件,现在有哪些比较火的前端框架,济南哪有实体爬虫店地址,php与flash交互,福州seo推广知识,外贸网站 中英,后台修改网页源代码工具,便捷支付模板lzw

手机版会员视频解析源码,ubuntu桌面共享设置,爬虫技术盗用数据,重置php,长沙推广seolzw

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 ame 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/f [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 ame 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/f [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/ | +--------------------------+----------------------------+