MySql 中文亂碼 utf8 latin1

話說2008 年搬家既時候, 由LifeType upgrade 去WordPress, 因為MySQL database default latin1 既關係, 有很多comment 亂碼. 當時只有文章有backup, comment 已經 dump 左落.sql file, 唔識搞.

最近Google 其他野冇意間見到有關encoding, 終於救返了.

==============================

e.g. backup.sql 有鬼畫符的文字：è€è±†ä½ åƒåŠ é›»ç®—ç›ƒå‘€????

1. create database / tables, default collation utf8_generic_ci

2. import data

$ mysql -u user -ppassword < backup.sql

1	$ mysql -u user -ppassword < backup.sql

3. export again as latin1

$ mysqldump -u user -ppassword -B dbName --tables plog_articles_comments --default-character-set=latin1 > comments-latin1.sql

1	$ mysqldump -u user -ppassword -B dbName --tables plog_articles_comments --default-character-set=latin1 > comments-latin1.sql

4. replace all “latin1” to “utf8”

cp  comments-latin1.sql  comments-utf8.sql
sed -i -e "s/latin1/utf8/g" comments-utf8.sql

1 2	cp comments-latin1.sql comments-utf8.sql sed -i -e "s/latin1/utf8/g" comments-utf8.sql

5. final import back

$ mysql -u user -ppassword --default-character-set=utf8 < comments-utf8.sql

1	$ mysql -u user -ppassword --default-character-set=utf8 < comments-utf8.sql