民芸的プログラミング 〜ソフトウェア開発日記〜

アクセスカウンタ

zoom RSS MySQL の文字コード設定を後から変えるには

<<   作成日時 : 2009/03/08 15:54   >>

なるほど(納得、参考になった、ヘー) ブログ気持玉 9 / トラックバック 0 / コメント 0

プロジェクトも終盤に入り、あと一息といったところで、余計なことに気付いた。
MySQL の文字コードの設定をすっかり忘れていた。
調べてみると、全てのテーブルの character set がデフォルトの latin1 のままだった。

ユーザー主体のブラックボックステストではこのあたりの項目、全てパスしていたのでまったく気付いていなかった。
気付かないまま、utf8 でデータを大量に突っ込んでしまっていた。
latin1 設定のカラムに utf8 のデータ。今までよく動いていてくれたものだ。

ここからの回復作業、もう少しまとめてから書こうかとも思ったが、後になったら忘れそうなので、実作業をリアルにここに書き留めておくことにした。
参考にしたのは、MySQL のマニュアルと
http://www.amatiny.com/blog/2008/02/ruby-on-railsmysqlutf8.html
http://labs.uechoco.com/blog/2008/11/mysql_collate_character-set_modify.html
など。

ここで大事なのは、既に作ってしまったテーブルの変換作業と、今後に備えての MySQL の設定変更を分けて考えること。
私の場合、被害の拡大(?)を防ぐために先に MySQL の設定を変更した。
なので、既存のテーブルの変換作業が必要以上に面倒になった可能性がある。あくまで可能性として。

まず、MySQL の状態を確認。MySQL のコマンドラインで
> show variables like 'character_set%';
とやると、character_set_client だとか character_set_connection だとか、あらゆる文字コードが latin1 になっていることが分かる。

で、/etc/my.cnf に
default-character-set=utf8
という設定を[mysqld] のセクションと [mysql] のセクションにそれぞれ追加。
で、mysqld を再起動。
そして、mysql コマンドで接続し、
> show variables like 'character_set%';
とやると、今度は文字コードがすべて utf8 になっている。
以後は文字コードを明示しない限り、database を作ろうが、table を作ろうが、character set は utf8 になってくれる。

続いて、既存のテーブルの変換。
幸い、SE が気を利かせてくれていて、テーブル名に日本語文字を使うようなことはしていなかった。

まずは、
> ALTER DATABASE hoge DEFAULT CHARACTER SET utf8;
とし、今後作るテーブルにはデフォルトで utf8 が使われるように設定変更。

続いてそれぞれのテーブルに対して
> ALTER TABLE fuga DEFAULT CHARACTER SET utf8;
とし、今後テーブルにカラムを追加した場合に、デフォルトで文字コードが utf8 になるように設定変更。

そして最後にやっかいなのが、既にデータを入力してしまっているカラムの変更。
> ALTER TABLE fuga CHANGE hogehoge hogehoge VARCHAR(192) CHARACTER SET utf8 NULL DEFAULT NULL;
とやってテーブルを変更してから、SELECT文を使ってみたところ、日本語部分がすべて '????' と化けてしまっていた。
試しに SELECT INTO OUTFILE でデータをファイルに書き出してみたところ、何のコードに化けたのだかさっぱり分からない形に文字化けしたファイルが書き出されていた。
慌てて
> ALTER TABLE fuga CHANGE hogehoge hogehoge VARCHAR(192) CHARACTER SET latin1 NULL DEFAULT NULL;
とやって設定を戻し、SELECT INTO OUTFILE で再度データを書き出したところ、元通りの utf8 でデータが書き出されている。

とりあえずこれでバックアップが取れたので(先に取っておいたほうがよかった...)、
> TRUNCATE TABLE fuga;
で、テーブル内のデータを全件削除。
続いてもう一度
> ALTER TABLE fuga CHANGE hogehoge hogehoge VARCHAR(192) CHARACTER SET utf8 NULL DEFAULT NULL;
でカラムの文字コードを utf8 にセットしなおし(これをカラム数分繰り返す)、先ほど書き出したデータを LOAD DATA INFILE で読み込む。
今度は SELECT 文で、無事日本語文字を表示することができた。

latin1 から utf8 への変換において、本来なら、 VARCHAR(192) は VARCHAR(32) に書き換えてもいいはずなのだが、そのあたりの調査まで手がまわらなかったので今回はこれはこのままとした。
MySQL では 4.1 以降、VARCHAR は文字数指定でありバイト数指定ではないということなのだが、万が一、ドキュメントと実装に乖離があって、実はバイト数で実装されていたりした場合、既に入っているデータを失うことになりかねないので、ここでは大事を取った。
(utf8 → latin1 の場合は、大事を取ると、6倍の長さのカラムを用意するということ)

テーマ

関連テーマ 一覧


月別リンク

ブログ気持玉

クリックして気持ちを伝えよう!
ログインしてクリックすれば、自分のブログへのリンクが付きます。
→ログインへ
気持玉数 : 9
なるほど(納得、参考になった、ヘー) なるほど(納得、参考になった、ヘー) なるほど(納得、参考になった、ヘー) なるほど(納得、参考になった、ヘー) なるほど(納得、参考になった、ヘー) なるほど(納得、参考になった、ヘー)
驚いた 驚いた
ガッツ(がんばれ!)

トラックバック(0件)

タイトル (本文) ブログ名/日時

トラックバック用URL help


自分のブログにトラックバック記事作成(会員用) help

タイトル
本 文

コメント(0件)

内 容 ニックネーム/日時

コメントする help

ニックネーム
本 文
MySQL の文字コード設定を後から変えるには 民芸的プログラミング 〜ソフトウェア開発日記〜/BIGLOBEウェブリブログ
文字サイズ:       閉じる