mysql中文乱码解决方案

一 问题描述

当往表里插入中文数据时，报错：

mysql> insert into t(id,nam) values(1,'丹丹');

ERROR 1366 (HY000): Incorrect string value:'\xE4\xB8\xB9\xE4\xB8\xB9' for column 'nam' at row 1

二 出错原因

该表或者列的字符集与当前连接的字符集不同导致。

mysql> show create table t;
CREATE TABLE `t` (
 `id` int(11) DEFAULT NULL,
 `nam` varchar(300) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
----安装完5.6.33后，默认字符集是：
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| 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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

由于character_set_database值是latin1,因此默认创建的表字符集也是latin1。

但是character_set_client，character_set_connection，character_set_results是utf8，因此插入中文时会报错。

/*

各参数含义如下：

*/

三 解决办法

3.1 临时方案

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| 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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)
 
再次插入就没有再报错了。
mysql> insert into t(id,nam) values(1,'丹丹');
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from t;
+------+--------+
| id  | nam    |
+------+--------+
|   1 | 丹丹   |
+------+--------+
但是set names latin1;命令是临时性的。当退出该会话，重新登录时，又变回了utf8：
mysql> select * from dan.t;
+------+--------------+
| id  | nam          |
+------+--------------+
|   1 | 丹丹       |
+------+--------------+
1 row in set (0.00 sec)

3.2 永久方案

修改配置文件，把默认字符集改成utf8。再重新导入数据。

3.2.1 备份字符集非utf8的表数据

#查看下除了系统自带的表外，有哪些表的字符集非utf8
mysql> select table_schema,table_name,TABLE_COLLATIONfrom information_schema.tables where table_schema notin('information_schema','performance_schema','mysql','test') andTABLE_COLLATION not like 'utf8%';
+--------------+------------+-------------------+
| table_schema | table_name |TABLE_COLLATION   |
+--------------+------------+-------------------+
| dan          | t          | latin1_swedish_ci |
| dba          | t          | latin1_swedish_ci |
| dba          | t2         | latin1_swedish_ci |
| jiao         | t          | latin1_swedish_ci |
| sds          | t          | latin1_swedish_ci |
+--------------+------------+-------------------+
#查看下这几个字符集为latin1的表所在的数据库都有哪些表：
mysql> selecttable_schema,table_name,TABLE_COLLATION from information_schema.tables where table_schemain ('dan','dba','jiao','sds');
+--------------+----------------+-------------------+
| table_schema | table_name     | TABLE_COLLATION   |
+--------------+----------------+-------------------+
| dan          | t              | latin1_swedish_ci |
| dba          | t              | latin1_swedish_ci |
| dba          | t2             | latin1_swedish_ci |
| jiao         | t              | latin1_swedish_ci |
| sds          | EOS_DICT_ENTRY |utf8_general_ci   |
| sds          | t              | latin1_swedish_ci |
+--------------+----------------+-------------------+

发现sds还有一个为utf8的表。所以需要单独备份sds.t

#备份下其余那几个库：

[root@slave2 mysql]# mysqldump -u root-psystem@123 --default-character-set=latin1 -B dba dan jiao  > /download/bak/3.bak

#备份sds.t表

mysqldump -u root -psystem@123--default-character-set=latin1 sds t > /download/bak/sds_t.bak

 /*

--default-character-set 表示设置以什么字符集连接

备份文件中会包含：SET NAMES latin1且表的插入语句不会出现乱码，如：

INSERT INTO `t` VALUES (1,'丹丹');

如果备份时没有指定选项--default-character-set，默认会使用utf8进行备份，则备份文件中会包含SET NAMES  utf8,且表插入语句会出现乱码，如：

INSERT INTO `t` VALUES (1,'丹丹');

*/

3.2.2 修改备份文件中的字符集

修改备份文件，

将SET NAMES latin1改成SET NAMES utf8,

将DEFAULT CHARACTER SET latin1改成DEFAULT CHARACTERSET utf8,

将DEFAULT CHARSET=latin1改成DEFAULT CHARSET=utf8

cd /download/bak

sed -i 's/SET NAMES latin1/SET NAMESutf8/g' 3.bak

sed -i 's/DEFAULT CHARACTER SETlatin1/DEFAULT CHARACTER SET utf8/g' 3.bak

sed -i 's/DEFAULT CHARSET=latin1/DEFAULTCHARSET=utf8/g' 3.bak

 

由于sds_t.bak只备份了sds.t表，没有备份数据库，

没有类似CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sds` /*!40100 DEFAULTCHARACTER SET latin1 */;的语句

因此只需要修改SET NAMES latin1及表的字符集配置DEFAULT CHARSET=latin1。

sed -i 's/SET NAMES latin1/SET NAMESutf8/g' sds_t.bak

sed -i 's/DEFAULT CHARSET=latin1/DEFAULTCHARSET=utf8/g' sds_t.bak

 

3.2.3 修改配置文件中的字符集

在[mysqld]下添加：

character_set_server=utf8

重启mysql，发现已改变

mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| 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/local/mysql/share/charsets/ |

3.2.4 导入数据

mysql -u root -psystem@123 < 3.bak

mysql -u root -psystem@123 sds <sds_t.bak

3.2.5 验证

mysql> select * from dan.t;
+------+--------+
| id  | nam    |
+------+--------+
|   1 | 丹丹   |
+------+--------+
1 row in set (0.00 sec)
 
mysql> select * from sds.t;
+------+--------+
| id  | name   |
+------+--------+
|   1 | 焦焦   |
|   3 | 焦焦   |
|   5 | 焦焦   |
+------+--------+
3 rows in set (0.00 sec)

已不乱码