<< >>

2006-10-11 [長年日記]

● [MySQL] 文字化け問題を本気で直す

これは、MySQL4.1から起きるようになった文字化け問題は有名であるが、個人的には、DBがlatin1のままでも何故かUTF8が正しく扱えていたので気付かないフリをしていたのだが、それは丁度虫歯に気付いているけど痛くないから歯医者に行かないのと同じで単なる問題の先送りに他ならず、当然のごとく猛烈な痛みとしてある日突然やってくることになるのだが、この場合は「mysqldump が時々化ける」という気持ち悪い問題となって自己主張を始めてきたので、いよいよ観念して本格的に調べてみることを決意した男の記録である。

● コマンド

まず何よりも MySQL で現在の文字コードを調べる方法がわからなかったのだが、以下の2つを知っていれば大丈夫のようである。

コマンド説明
status現在の設定を表示
show variables like "char%";文字コード関係(ぽい)変数を表示

● 現状

上のコマンドを利用して現状を確認してみる。

現在の設定

mysql> status;
--------------
mysql  Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i386) using readline 4.3

Connection id:          36
Current database:       staff2006
Current user:           maiha@localhost
SSL:                    Not in use
Current pager:          lv
Using outfile:          ''
Using delimiter:        ;
Server version:         4.1.20
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 1 hour 4 min 14 sec

Threads: 2  Questions: 54354  Slow queries: 0  Opens: 468  Flush tables: 1  Open tables: 64  Queries per second avg: 14.103
--------------

何も考えていないだけあって、"characterset" 関係が全て "latin1" になっている。この部分が違っていると、MySQLサーバ(←多分A型)が興奮し、勝手に文字コードを変換してしまう。その際、"utf8" <-> "latin1" といった不可能な変換が実行されると確実に "???" や意味不明の文字化けを引き起こす。これが4.1文字化け問題である。しかし、"utf8" が何ひとつ入っていない上記のような純粋バカ状態が幸いし、変換が実行されないために Rails 上では動作しているようだ。まさに、マイナスかけるマイナスはプラス。バカは救い難いが完全なバカは意外と使える状態。

文字コード関係の変数の値

mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

変数を見てみるとさらに "character_set_system" という変数が現れており、これだけが "utf8" になっている。恐らくこれが原因で mysqldump だけが化けているのであろう。これを "latin1" にできればそれも解決しそうだが、原因がわかった以上、わざわざバカの完全体を目指す必要もなく、これらの変数を全て utf8 に統一するのが本来の正しい姿である。そう、どうしようもない不良が今日から優等生に生まれ変わるのである。

● set names

ここで、"set names" という文字コードを設定コマンドを使ってみる。MySQL独自のSQLのクエリとして動作するようなので、mysql コマンドから接続して実行してみる。

set names による設定

mysql> set names utf8;
mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

"set names" コマンドによって、クライアントとコネクションと結果セットの文字コードは指定できるようだ。ちなみに、Rails の database.yml にある "encoding" の値は AR のコネクション接続時に "set names" の値として利用される。(shugo乙)。先日よかれと思って "encoding: utf8" を追加したところ Rails レベルでも化けるようになって驚いたのだが、中途半端な知識によって純粋バカ状態が上記のように破壊されてしまい、文字コード変換が誘発されてしまったと推測できる。

つまり、このコマンドは接続側と接続情報にのみ影響を与え、既に作成されたデータベース(とその内部の文字列が利用しているコード)とサーバの文字コードには影響を与えない。クライアントの勝手な言い分に従って、DB内の文字コードがその度に一斉に変換されるなんて実装はありえないので、これは理解できる。ということで、後は残りの2つを utf8 にする設定を探せばOK。

● DBの作成方法

ちなみに今の状態でDBを作成してみると

普通にDBを作成

% mysqladmin create maiha
mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

文字コードは "latin1" になる。このデフォルト値はコンパイル時に指定することができるが、このデフォルト値のデフォルト値(ややこしい)は "latin1" なので、rpm, deb 等のパッケージインスコの場合は "latin1" の可能性が高い。

マニュアルによると "--default-character-set" オプションがあるようなのでそれで指定してみると、

文字コードを指定してDBを作成

% mysqladmin create --default-character-set=utf8 maiha
mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

なぜか "latin1" のままである。赤の MySQL さん、ここは "utf8" になって頂きたかった。(児玉清)

● /etc/my.cnf の設定

MySQL は空気(実行時のオプション)が読めないようなので、設定ファイルではっきりと明示しておくしかなさそうだ。

/etc/my.cnf

[mysqld]
default-character-set=utf8
skip-character-set-client-handshake

1行目でデフォルトの文字コードを指定し、2行目は「余計なことはするな」という命令。(MySQL4.1.5で実装)。これによってサーバとクライアントの文字コードが違っていても内部変換をしなくなる。余計なものを実装して、その余計なものを抑止するオプションを実装してくれたMySQLに乾杯！設定は上記のように "mysqld" セクションだけでOK。サーバの設定を変更したので、MySQLは忘れずに再起動しておく。

DBを作ってみる

% mysqladmin create maiha
mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

おー、すいーと、ぴーちっち！オプションなしで作成しても、サーバの設定(default-character-set)があるため、望み通りに utf8 で統一されている。

ダンプファイルの確認

mysql> insert into members values ('舞波');
Query OK, 1 row affected (0.00 sec)

% mysqldump maiha
...
/*!40101 SET NAMES utf8 */;
...
INSERT INTO `members` VALUES ('舞波');

化けてない！

チャラチャラッチャラッチャッチャー！
MySQLはレベルが上がった。
mysqldump が化けなくなった！
database.yml の "encoding: uft8" が不要になった！
テーブル作成時の "DEFAULT CHARSET=utf8" が不要になった！

● 結論

  • 文字コードは全部統一すべし (できれば)
  • "status" と "show variables like "char%";" コマンド重要

● 参考

● 今までお世話になったコマンド

  • mysqldump --default-character-set=binary ...
  • mysqldump --default-character-set=latin1 ...
本日のツッコミ(全33件) [ツッコミを入れる]
_ snowin (2006-10-19 16:09)

ひょんなことからここにたどり着きました。<br><br>mysqlの文字化けとの軌跡をテンポよく、なおかつユーモアがあって楽しく読ませていただき勉強にもなりました。<br><br>こんな感じだとこういう内容の文献もすらすら読めちゃいますね。こんな感じでこれからも書いていってください。

_ yosizu (2006-10-20 09:26)

すばらしい、感動すた。

_ ちょ (2006-10-20 10:17)

ワロタｗ、でも勉強になりますた。

_ ゆ (2006-10-21 01:35)

かっちょいい！　アンタほんまもんの男や！

_ T.Y (2006-10-21 03:21)

Windowsの場合、コマンドプロンプトはCP932しか使えませんから、コマンドプロンプトからMySQLを操作しようとする場合には、サーバ・クライアント間で文字コードを変換してもらわなければなりません（Windowsだからといって、文字コードをCP932に統一したくはありません）。また、my.cnfをいじれない場合もありますから、「データベースの文字コードとクライアント・プログラムの文字コードを常に気にすること」という結論のほうがいいのではないでしょうか

_ Akira51 (2006-10-21 05:02)

配色・デザインのすっきりしたサイトですね。

_ 从 ’w’) (2006-10-24 07:10)

本文に反映しました。ありがとう！＞T.Y

_ 本 (2006-12-03 16:49)

いや〜〜おかげで長年の悩みが解消しますた。<br>色々ググって調べたんだけど、文字化け解消しなかったんです・・・てかロクに読んでなかったんだよね(;´Д`) <br>わかりやすく解説してくれてありがとう！感謝♪

_ umo (2006-12-07 21:38)

すっきり〜！ずっとずっとわからなくてぐぐりまくっても<br>わからなかったのがこの１ページですっきりなおりました！！<br>やっとこれでwindowsにxoops計画が進められます♪<br>ギャグのセンスとわかりやすい解説！感動です。

_ hatta (2007-01-01 16:19)

マイナスかけるマイナスはプラスのくだり、笑わせてもらいました。年始早々感謝♪感謝♪<br>そういえば、Rails入門のp309一番下のassert_raise(*args)は、asser_nothing_raisedではないでしょうか。<br>OKまたは既出かもしれませんが一応ご報告まで。

_ hatta (2007-01-01 16:48)

実はまだMySQL Query Browserが化けてて...<br>前のプロジェクトで<br>[mysqld]に<br>skip-character-set-client-handshake<br>みたいなオプションを2つ3つ指定したらUTF8で化けなくなったのですが忘れました

_ 舞波 (2007-01-04 10:58)

うわー、ほんとだ!なにこのコピペ厨＞assert_raise<br>おっしゃる通りです。正誤表に追加します。<br>ありがとう!＞hatta

_ domino (2007-01-30 23:53)

感動した。<br>文字化け不完全バカから抜け出せました。<br>ありがとう！

_ cat (2007-04-15 12:38)

児玉清のところで笑いました

_ おー (2007-04-25 11:39)

すごい！！<br>文字化けが直った！<br>早くこのサイトに気づけばよかった。。<br>本当にありがとうございました！！

_ hujiko (2007-04-26 11:10)

とてもわかりやすい解説で簡単に直せました！<br>ありがとうございました

_ このサイトすごい (2007-05-02 09:44)

分かりやすい解説ありがとうございました。<br>すぐに文字化けが直ってしまいましたｗ<br><br>あれだけ悩んだのに。。。（ｗ

_ ina (2007-05-05 12:19)

助かりました。<br>ありがとうございます！！

_ kiyoshi kodama (2007-06-21 17:31)

"全部統一すべし"そのとーり!結構!!

_ iR3 (2007-06-27 22:00)

最近MySQL始めました^^　/etc/my.cnfの指定にたどり着き助かりました！！nksk乙

_ Kazuhiro Fujiwara (2007-07-08 23:26)

Rails勉強中。<br>この文字化けで頓挫していたのですが晴れて問題解決ですっ!<br>世のためにも自分も何か役に立ちそうな経験をblogに綴っていこうかと本気で思います。

_ ムコスタ (2007-08-07 15:23)

わかりやすい上におもしろいです。<br>ありがとうございました。

_ ちゃる (2007-10-20 22:26)

PHPでの開発で同じ現象にぶち当たって悩んでましたが、このページを読んで無事解決しました！<br>大変楽しく解説されているうえに勉強になりました。<br>ありがとうございました！

_ komchiro (2007-12-19 18:44)

今までの悩みがこれで解決しました。<br>本当にありがとう m(__)m

_ 長年悩んだハゲ (2008-01-29 16:54)

あなた神様？<br>早くこのサイトに出会っていたら禿げなくてすんだかも・・・

_ ビロクマ (2008-03-19 11:14)

トラブルが解決しました。ありがとうございました。

_ そな (2008-06-19 11:33)

・・・好き。

_ 一見通りすがり (2008-09-03 06:50)

文字化けで悩んでました。<br>行目は「余計なことはするな」という命令で解決です。<br>助かりました。ありがとうございます。

_ ぬらりひょん (2009-01-20 20:19)

児玉清吹いたｗｗｗｗｗ

_ ラジヲ (2009-02-28 18:28)

これで家に帰れます・・・

_ ちょき (2009-04-11 17:09)

ありがとうございました！

_ 初心者 (2009-05-19 09:35)

くまくま。あんたは救世主だ〜

_ U-NO (2009-09-10 15:25)

解決しました！<br>ありがとうございました。

Generated by tDiary version 2.2.1
Powered by Ruby version 1.8.7