MySQL 文字化けについのてまとめ
MySQLの文字化けに関して、ネットとかに散らばっている情報をかき集めたりした自分用メモ。
１．文字化けに関わる何か
MySQL4.1を境に
追加・変更された幾つかの機能によって発生しやすくなったらしい。
・[新規]Unicode(utf-8およびucs2)のサポート
・[新規]文字コードの自動変換機能の追加
・[変更]カラム,テーブル,データベース単位のキャラクタセット指定
| Shift-JIS | EUC-JP | Unicode | |
|---|---|---|---|
| 4.0 | sjis | ujis | |
| 4.1 | sjis/cp932 | ujis | utf8/ucs2 |
| 5.0 | sjis/cp932 | ujis/eucjpms | utf8/ucs2 |
※4.1->5.0 の変更で２種類から６種類に文字コードが増えた
捕捉１） cp932(IANA登録名:Windows-31J)
シフトJISを拡張したWindowsに置ける正しい文字コード。基本的にShift-JISと文字コードとしての違いはないが、他の文字コードに変換する場合に一部の文字が異なる挙動を引き起こす。
・「〜」sjis (0x81, 0x60) => Unicode(0x301C)
CP932(0x81, 0x60) => Unicode(0xFF5E)
・「−」sjis (0x81, 0x7C) => Unicode(0x2212)
CP932(0x81, 0x7C) => Unicode(0xFF0D)
sjisとして変換するか、CP932として変換するかで異なってくる。文字化けする文字から「WAVE DASH - FULLWIDTH TILDE問題」とも言う。また「①」(CP932では0x87,0x40、Unicodeでは0x2460)等は、sjisには存在しない。従って、sjisよりもCP932を文字コードとして利用した方がアプリとして良いかも。
捕捉２） eucjpms
EUC-JPの亜種。Microsoftが策定したWindows-31JのEUC-JP互換表現。通常のEUC-JPには無いWindows-31Jの拡張漢字や外字領域が用意されている。使わない方が良いかも。というか多分使わない。
捕捉３） ucs2
Unicodeの拡張が進んだ今日では、BMP部分のみしか符号化出来ないUCS-2の出番はUTF-8にその座を譲っています。
意識する必要はないのかと思ったが、サーバ上の自動文字変換処理に於いて、中間コードとして利用されているらしい。
２．要注意機能
１．で挙げた機能のうち、最も文字化けに関わっているのが「文字コードの自動変換機能」。便利ではあるが、文字化け誘発の温床となっているらしい。
・システム
クライアントからDBへデータを投入するフローに、全部で５箇所の文字コード設定ポイントがある。
1)[client]クライアント文字コード 2)[server]サーバ全体文字コード 3)[server]対象DB文字コード 4)[server]対象テーブル文字コード 5)[server]対象カラム文字コード
サーバサイドの文字コードの決定フローは、カラム文字コードが未設定ならテーブル文字コード、テーブル文字コードが未設定ならDB文字コードというように、5)から2)に向かってバブル。
３．文字コード変換処理
文字コードの変換はダイレクトに行われる訳ではなく、いったんUCS2を経由して行われる。
■変換テーブル(みづらい)
| クライアントサイド | サーバサイド | |||
| binary | エスケープ 処理 |
エスケープ解除処理を行う | 変換なし | binary |
| サーバと同一文字コード | クライアントと同一文字コード | |||
| サーバと異なる文字コード | エスケープ処理 | 中間コードUCS2に変換 | UCS2からサーバ上の設定文字コードへ変換 | |
捕捉） エスケープ処理
文字コードの変換処理とは別に「エスケープ処理」と「エスケープ解除処理」が行われる（「0x5C」の付与・削除）。
■エスケープ対象
| 対象文字 | コード |
|---|---|
| NULL | 0x00 |
| LF | 0x0A |
| CR | 0x0D |
| Ctrl+Z | 0x1A |
| ￥ | 0x5C |
| " | 0x22 |
| ' | 0x27 |
クライアントとサーバの文字コードが一致すると文字コードの変換が発生しないので文字化けが起こらないと思われているが、それとは関係なくエスケープ処理や解除処理が自動的に行われるので、文字化けが発生する事がある。「表」はsjisで「0x955C」であるが、エスケープ解除処理によって「0x95」となってしまう。
※エスケープ解除処理では、クライアント側の文字コードがsjisやcp932などのマルチバイト文字の場合「0x5C」を削除しない。
=> これを防止するには skip-character-set-client-handshake を指定して、自動変換機能を切ってしまう事。
| 内容 | 実施タイミング | 変更対象 | 例 |
|---|---|---|---|
| クライアント側の文字コードを指定 | クライアント | クライアント | set names cp932 |
| サーバ側の文字コードを指定 | サーバ | サーバ | default-charactes-set=cp932 |
| データベース,テーブル カラムの文字コードを設定 |
サーバ | サーバ DB,table,col |
SQLステートメントで実施 |
| 接続時のクライアント側 文字コードを指定 |
サーバ | クライアント | init_commect='set names cp932' |
| サーバ側と同じ文字コードに設定(文字コード自動変換防止) | サーバ | クライアント | skip-character-set-client-handshake |
※init_commectによって、クライアント接続時にクライアント側の文字コードの初期値を指定出来るが、全てのクライアントに適用されるので、クライアントによって文字コードが異なる場合には、結局クライアント個別に設定する必要が発生する。
■/etc/my.conf
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
[client]
default-character-set = utf8
[mysqld]
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8
skip-character-set-client-handshake
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- -
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
５．ちなみに
文字コードの確認(show variables like 'char%')
mysql> show variables like 'char%' +--------------------------+-----------------+ | Variable_name | Value | +--------------------------+-----------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local〜〜 | +--------------------------+-----------------+ 8 rows in set (0.00 sec)
=> character_set_client, character_set_database, character_set_serverが全てlatin1なので、文字コード変換は行われない環境である。
latin1:アルファベットに英語以外のラテン系欧州語で使われるアルファベットを集めた文字セット
・character_set_client
クライアントから実行されたSQL文を指定された文字コードで解釈する
・character_set_connection
基本的には、character_set_clientからこの設定文字コードへ変換してDBへ格納する。より正確に言うなら、文字セットイントロデューサ要素が省略された場合にSQLに含まれる文字列の文字コードとして利用される。 ・・・・・ INSERT INTO hoge(huga) VALUES('日本語'); これは普通にcharacter_set_connectionの文字コードで格納される。 INSERT INTO hoge(huga) VALUES(_sjis'日本語'); こんな感じに「_文字コード」を値の前に付けてやると、その文字コードで格納される。
・character_set_database
デフォルトのサーバキャラクタセット
・character_set_results
クライアントへ返す文字列（クエリの結果）のキャラクタセット
・character_set_server
デフォルトのDBキャラクタセット
・character_set_filesystem
LOAD DATA INFILE や SELECT ..... INTO OUTFILE などのステートメントや LOAD_FILE()関数に対して、この変数でファイル名とリテラルの文字列を読み取る。ファイルを開けようとすると、ファイル名が character_set_client から character_set_filesystem に変わる。デフォルトはbinaryである。 マルチバイトのファイル名を利用できるシステムでは、異なる値を使用する事が好ましい。例えば、UTF-8でファイル名を表示しているシステムの場合は、character_set_filesystem を'utf8'にセットする(MySQL 5.1.6 実装)
・character_set_system
テーブルやカラム名などの識別子の書出しにサーバが使用するキャラクタセットで常にutf8固定
・character_sets_dir
MySQLで使われる文字コードファイルが保存されているディレクトリパス
