[Linux] MySQL 資料庫 charset 從 latin1 轉 utf8 筆記 @ Ubuntu 12.04

昨天剛順利移機（[Linux] 透過 MySQL Replication 之 Master Slave 切換進行 DB server 移機 @ Ubuntu 12.04），移機後自以為沒事就收工了，結果發現 MySQL DB Server 預設的 charset 是 latin1 而非 utf8 ，接著就開始改設定檔（[Linux] MySQL Server & Client characterset 設定 @ Ubuntu 12.04），以為改完沒事...才發現大條的才剛開始。

那就是昨天移機後的資料，都是以 latin1 編碼儲存進去的：

mysql> \s
...
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
...
mysql> SELECT message FROM TABLE;

會有一堆看不懂，改用 latin1 編碼就看懂了

mysql> SET NAMES latin1;
mysql> \s
...
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
...
mysql> SELECT message FROM TABLE;

接著就在想到底該怎樣轉 XD 因為 TABLE 的 charset 是 UTF8 編碼，不管怎樣做 convert, cast 似乎都沒成效，最後找到一些招術，幸運地真的解掉，流程：

1. 先備份資料庫
2. 先確認到底是哪幾筆資料有問題，用 mysqldump db_name table_name --where "id>=100 AND id <=200" > data.sql 的方式匯出
3. 修改 data.sql 的 table 名稱、編碼定義（例如 TABLE_LATIN1、DEFAULT CHARSET=latin1），記得要把 table 改名稱，以免匯入時會 DROP 之前的資料
4. 匯入 data.sql
5. 建立一個新 table ，其資料表跟 TABLE_LATIN1 一樣，但 DEFAULT CHARSET=utf8，名為 TABLE_UTF8
6. 設定 mysql 編碼為 latin1 ，並從 TABLE_LATIN1 取出資料，以 CONVERT(message USING BINARY) 存入 TABLE_UTF8 中
7. 設定 mysql 編碼為 utf8 ，接著查證 TABLE_UTF8 是否正確，若正確的話，可以把資料從 TABLE_UTF8 更新到為本的 table 中

連續動作：

$ mysqldumo -u account -p db_name table_src_name --where 'id>=100 AND id<=200' > table_src_name.latin1.sql
$ vim table_src_name.latin1.sql
-- ...
DROP TABLE IF EXISTS `table_src_name_latin1`;
CREATE TABLE `table_src_name_latin1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
-- ...

$ mysql -u account -p -D dbname < table_src_name.latin1.sql
$ mysql -u account -p
mysql> use db_name;
mysql> CREATE TABLE `table_src_name_utf8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
mysql> \s
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
mysql> SET NAMES latin1;
mysql> \s
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
mysql> INSERT INTO table_src_name_utf8 SELECT id, CONVERT(message USING BINARY) FROM table_src_name_latin1;
mysql> SET NAMES utf8;
mysql> SELECT * FROM table_src_name_utf8;
mysql> UPDATE table_src_name as d, table_src_name_utf8 as s SET d.message = s.message WHERE d.id = s.id;