mysql匯入資料之亂碼分析及解決辦法

亂碼有很多種情況引起，不過通常情況都是由於各種編碼不一致問題導致，今天遇到的亂碼問題是：檔案編碼和資料庫連接編碼不一致的問題導致，由於要添加初始化資料，批量執行，採用source命令，

root@(none) 03:14:49>use test
Database changed
root@test 03:14:53>show create table rule\G;
*************************** 1. row ***************************
Table: rule
Create Table: CREATE TABLE `rule` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `name` varchar(64) NOT NULL COMMENT '規則名，如：分享',     
  `status` tinyint(4) NOT NULL COMMENT '狀態',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk;
root@test 03:15:06>show variables like "%character%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | gbk                              | 
| character_set_connection | gbk                              | 
| character_set_database   | gbk                              | 
| character_set_filesystem | binary                           | 
| character_set_results    | gbk                              | 
| character_set_server     | gbk                              | 
| character_set_system     | utf8                             | 
+--------------------------+----------------------------------+
7 rows in set (0.00 sec)

root@test 03:17:14>

可以看到用戶端串連進來的編碼方式是gbk,當我直接使用

root@(none) 03:19:02>source a.sql;

root@test 03:20:45>select * from rule;
+----+--------------------+--------------------------------+

| id | name          | status |

+----+--------------------+--------------------------------+
|  1 | 嫻囨按             |      0 |

|  2 | 鍏蟲敞鎺屾煖鎺ㄦ帹 |        0 |

+----+--------------------+--------------------------------+
2 rows in set (0.00 sec)

root@test 03:20:46>

亂碼，為什嗎？我用戶端的串連，表的編碼格式是一樣的！！

再看下a.sql的編碼方式：

$file a.sql
a.sql: UTF-8 Unicode text, with CRLF line terminators

檔案是utf8編碼，再運行下看看：

root@test 02:35:36>set names utf8;
Query OK, 0 rows affected (0.00 sec)

root@test 02:35:57>truncate table rule;
Query OK, 0 rows affected (0.00 sec)
root@test 02:36:04>source a.sql;
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
root@test 02:36:08>select * from rule; 
+----+--------------------+--------------------------------+
| id |  name          | status |
+----+--------------------+--------------------------------+
|  1 | 嫻囨按             |      0 | 
|  2 | 鍏蟲敞鎺屾煖鎺ㄦ帹 |      0 | 
+----+--------------------+--------------------------------+
2 rows in set (0.00 sec)

root@test 02:36:10>set names gbk;
Query OK, 0 rows affected (0.00 sec)

root@test 02:36:23>select * from rule;
+----+--------------+--------------------------------+
| id | name    | status |
+----+--------------+--------------------------------+
|  1 | 灌水         |     0 |
|  2 | 打醬油       |     0 | 
+----+--------------+--------------------------------+
2 rows in set (0.00 sec)

root@test 02:36:25>