Google+ Followers

2014年3月27日 星期四

[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;

沒有留言:

張貼留言