原创

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

二 出错原因

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

[sql]  view plain  copy
  1. mysql> show create table t;  
  2. CREATE TABLE `t` (  
  3.  `id` int(11) DEFAULT NULL,  
  4.  `nam` varchar(300) DEFAULT NULL  
  5. ) ENGINE=InnoDB DEFAULT CHARSET=latin1  
  6.    
  7. ----安装完5.6.33后,默认字符集是:  
  8. mysql> show variables like '%char%';  
  9. +--------------------------+----------------------------------+  
  10. | Variable_name            | Value                            |  
  11. +--------------------------+----------------------------------+  
  12. | character_set_client     | utf8                             |  
  13. | character_set_connection | utf8                             |  
  14. | character_set_database   | latin1                           |  
  15. | character_set_filesystem | binary                           |  
  16. | character_set_results    | utf8                             |  
  17. | character_set_server     | latin1                           |  
  18. | character_set_system     | utf8                             |  
  19. | character_sets_dir       | /usr/local/mysql/share/charsets/ |  
  20. +--------------------------+----------------------------------+  

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

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

/*

各参数含义如下:

*/

三 解决办法


3.1 临时方案

[sql]  view plain  copy
  1. mysql> set names latin1;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3. mysql> show variables like '%char%';  
  4. +--------------------------+----------------------------------+  
  5. | Variable_name            | Value                            |  
  6. +--------------------------+----------------------------------+  
  7. | character_set_client     | latin1                           |  
  8. | character_set_connection | latin1                           |  
  9. | character_set_database   | latin1                           |  
  10. | character_set_filesystem | binary                           |  
  11. | character_set_results    | latin1                           |  
  12. | character_set_server     | latin1                           |  
  13. | character_set_system     | utf8                             |  
  14. | character_sets_dir       | /usr/local/mysql/share/charsets/ |  
  15. +--------------------------+----------------------------------+  
  16. rows in set (0.01 sec)  
  17.    
  18. 再次插入就没有再报错了。  
  19. mysql> insert into t(id,nam) values(1,'丹丹');  
  20. Query OK, 1 row affected (0.02 sec)  
  21.    
  22. mysql> select * from t;  
  23. +------+--------+  
  24. | id  | nam    |  
  25. +------+--------+  
  26. |   1 | 丹丹   |  
  27. +------+--------+  
  28. 但是set names latin1;命令是临时性的。当退出该会话,重新登录时,又变回了utf8:  
  29. mysql> select * from dan.t;  
  30. +------+--------------+  
  31. | id  | nam          |  
  32. +------+--------------+  
  33. |   1 | ä¸¹ä¸¹       |  
  34. +------+--------------+  
  35. 1 row in set (0.00 sec)  

3.2 永久方案

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

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

[sql]  view plain  copy
  1. #查看下除了系统自带的表外,有哪些表的字符集非utf8  
  2. 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%';  
  3. +--------------+------------+-------------------+  
  4. | table_schema | table_name |TABLE_COLLATION   |  
  5. +--------------+------------+-------------------+  
  6. | dan          | t          | latin1_swedish_ci |  
  7. | dba          | t          | latin1_swedish_ci |  
  8. | dba          | t2         | latin1_swedish_ci |  
  9. | jiao         | t          | latin1_swedish_ci |  
  10. | sds          | t          | latin1_swedish_ci |  
  11. +--------------+------------+-------------------+  
  12. #查看下这几个字符集为latin1的表所在的数据库都有哪些表:  
  13. mysql> selecttable_schema,table_name,TABLE_COLLATION from information_schema.tables where table_schemain ('dan','dba','jiao','sds');  
  14. +--------------+----------------+-------------------+  
  15. | table_schema | table_name     | TABLE_COLLATION   |  
  16. +--------------+----------------+-------------------+  
  17. | dan          | t              | latin1_swedish_ci |  
  18. | dba          | t              | latin1_swedish_ci |  
  19. | dba          | t2             | latin1_swedish_ci |  
  20. | jiao         | t              | latin1_swedish_ci |  
  21. | sds          | EOS_DICT_ENTRY |utf8_general_ci   |  
  22. | sds          | t              | latin1_swedish_ci |  
  23. +--------------+----------------+-------------------+  

发现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,发现已改变

[sql]  view plain  copy
  1. mysql> show variables like '%char%';  
  2. +--------------------------+----------------------------------+  
  3. | Variable_name            | Value                            |  
  4. +--------------------------+----------------------------------+  
  5. | character_set_client     | utf8                             |  
  6. | character_set_connection | utf8                             |  
  7. | character_set_database   | utf8                             |  
  8. | character_set_filesystem | binary                           |  
  9. | character_set_results    | utf8                             |  
  10. | character_set_server     | utf8                             |  
  11. | character_set_system     | utf8                             |  
  12. | 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 验证

[sql]  view plain  copy
  1. mysql> select * from dan.t;  
  2. +------+--------+  
  3. | id  | nam    |  
  4. +------+--------+  
  5. |   1 | 丹丹   |  
  6. +------+--------+  
  7. 1 row in set (0.00 sec)  
  8.    
  9. mysql> select * from sds.t;  
  10. +------+--------+  
  11. | id  | name   |  
  12. +------+--------+  
  13. |   1 | 焦焦   |  
  14. |   3 | 焦焦   |  
  15. |   5 | 焦焦   |  
  16. +------+--------+  
  17. rows in set (0.00 sec)  

已不乱码

 

正文到此结束
Loading...