[MySQL] 資料庫latin1塞big5資料，轉換成utf8

環境 CentOS 6.4_x64 + MySQL 5.1_x64

將資料庫字集編碼是latin1，但是存入的資料卻是big5的資料，轉換成utf8。

將底下shell script存成一個檔案，例如:/tmp/_mysql2utf8.sh
假設資料庫名稱為: mydb，帳號:root，密碼:xxxx，請執行：
sh /tmp/_mysql2utf8.sh mydb
會產生一個 mydb_utf8 的資料庫

#!/bin/bash
pwd='xxxx'
dir=`pwd`

[ $# != 1 ] && echo "請輸入資料庫名稱 database_name" && exit 0

tmppath='/tmp'
cd $tmppath

dbs=$1
databases="$(mysql -uroot -p$pwd -Bse 'show databases like "'$dbs'%"')"
for db in $databases
do
  db1=$db'_utf8'
  workpath=$tmppath'/'$db
  test -d $workpath && rm -rf $workpath
  test ! -d $workpath && mkdir -p $workpath
  chmod 777 $workpath
  create=$workpath'/create.sql'
  create1=$workpath'/create_utf8.sql'
  echo 'DROP DATABASE IF EXISTS '$db1';' > $create
  echo 'CREATE DATABASE '$db1';' >> $create
  echo 'USE '$db1';' >> $create
  mysqldump $db -uroot -p$pwd --opt --default-character-set=latin1 --no-data >> $create
  cat $create | sed -e "s/AUTO_INCREMENT=[1-9]* //g" | sed -e "s/CHARSET=latin1/CHARSET=utf8/g" | sed "s/InnoDB/MyISAM/g" > $create1
  mysql -uroot -p$pwd --default-character-set=utf8 < $create1

  tables="$(mysql $db -uroot -p$pwd -Bse 'show tables')"
  for tname in $tables
  do
    txt=$workpath'/'$tname'.txt'
    txt1=$workpath'/'$tname'_utf8.txt'
    mysql -uroot -p$pwd -Bse 'SELECT * FROM `'$db'`.`'$tname'` INTO OUTFILE "'$txt'"'
    iconv -c -f big5 -t utf-8 $txt -o $txt1
    mysql -uroot -p$pwd --default-character-set=utf8 -Bse 'LOAD DATA INFILE "'$txt1'" INTO TABLE `'$db1'`.`'$tname'` CHARACTER SET utf8'
  done

  rm -rf $workpath
done