MySQL5文字化け調査

2009-05-19

昨日、tetsuya_odaka氏から質問が舞い込んできた。「PHPからMySQL5にDB アクセスすると文字化けする。因みにEclipse(DBViewer使用)からもやってみたが同じ現象になる」というもの。Syracavaにも関係の深い話なので調査をしてみた。調査の参考にさせてもらったサイトは次の通り。

では早速、覚書。

調査概要

まずは調査概要から。次のような概要で調査。

１．Java

　Windows XP Pro SP2上で動作するMySQL5.0.67を用いて、JDBCでのアクセスによる文字化け状況を確認。

　JDBCは、Connector/J 5.0.8を使用してEclipse Ganymede+DBViewer 1.1.0からアクセスする。

２．PHP

　Apache2.2.10+PHP5.2.6から同様のMySQLにアクセスして文字化け状況を確認。

　MySQLへのアクセスは MySQLi を使用する。

MySQL環境

次にMySQL5.0.67の動作環境を確認。まずはmy.ini。以下の内容は文字コードセットに関する設定内容を抜粋したもの。

[client]
port=3306

[mysql]
default-character-set=utf8

[mysqld]
default-character-set=utf8

クライアント、サーバー共にUTF8が設定されていることが分かる。

次に実際の環境ではどうか。コマンドSTATUSを使って確認してみる。

xxxxx characterset と表記されている部分に注目。クライアント、サーバー共にUTF8が設定されていることが分かる。

検証用データベースの準備

検証用データベースをMySQL5に作成する。

CREATE DATABASE SAMPLEDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE SAMPLEDB2 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

念のためにSHOW CREATE DATABASEコマンドを使って作成内容を確認。

sampledbに予めコマンドラインでテーブルを作っておく。Eclipseでアクセスしたときに文字化けしないかのため。

CREATE TABLE SAMPLEDB.EXM_TABLE01 (
    ID CHAR(10) NOT NULL,
    NAME VARCHAR(50),
    PRIMARY KEY(ID)
);

sampledb2は空のままとする。これはEclipseで作成したときの状態を見るため。

検証（Java）

Javaの方から検証を。Eclipseを立ち上げてDBViewer*1パースペクティブをアクティブにする。新たな定義をsampledbとsampledb2各々作成する。

sampledbをダブルクリックすると、コマンドラインで作成したテーブルが見つかるはず。テーブルEXM_TABLE01はまだ空なのでINSERT文を発行する。

ではテーブル一覧を表示してみる。

うーーんキレイだ･･･。問題なく表示されている。ではsampledb2についてもやってみる。まずテーブルをEXM_TABLE100として先ほどのEXM_TABLE01と同じ構造のものを作り、データを登録。で、一覧表示・・・問題ないですねえ。

結論

Windows上で動作するMySQLだが、おそらくは他のOSでも同様の結果が得られるものと思われる。

Javaでは文字化けする可能性は、この環境設定の状況からみて低いものと考えられる。

検証（PHP）

今度はPHP。まずは検証用に以下のコードを作って実行してみる。

<!--
/*
 * @(#) dao_exm_table01.php
 */
-->
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html;charset=utf-8">
    <style type="text/css">
    * {margin:10px;font-family:'DejaVu Sans';line-height:'150%';}
    button {width:70px;padding:5px;border:1px solid #ccc;}
    button:hover {background-color:#cf3;}
    .contents {position:relative;margin:20px;}
    .resultset {position:relative;margin:20px 20px;}
    table {border-collapse:collapse;border-width:1px;border-style:solid;border-color:#ccc;}
    th {padding:5px;background-color:orange;}
    td {padding:5px;background-color:#fff;}
    </style>
  </head>

<body>

<h2>DAO Test for MySQL5.0.67</h2>

<button type="button" onclick="history.back();">BACK</button>

<div class="contents">

<?php
/**
 * 検証用PHPコード
 * SAMPLEDB.EXM_TABLE01にアクセスして一覧表示をする。同時にMySQLサーバーのデフォルト文字コードセットを
 * 取得する。MySQLへのアクセスにはMySQLiを使用する。
 */

// 接続と検証
$mysqli = new mysqli("localhost", "root", "this is root user");
if (mysqli_connect_errno()) {
    printf("<b style='color:#f00'>Connect failed</b>: %s<br>\n", mysqli_connect_errno());
    exit();
}

// サーバーのデフォルト文字コードセットを取得
$charset = $mysqli->character_set_name();
printf("<b>Current character set</b> is <b style='color:#00f'>%s</b><br>\n", $charset);

// 一覧表示
if ($result = $mysqli->query("select * from sampledb.exm_table01")) {
    printf("<b>Select returned</b> <b style='color:#00f'>%d rows</b>.<br>\n", $result->num_rows);
    $finfo = $result->fetch_fields();
    
    printf("<div class='resultset'>\n");
    printf("<table border='1'>\n");
    printf("<tr>");
    foreach ($finfo as $val) {
        printf("<th>%s</th>", $val->name);
    }
    printf("</tr>\n");
    while ($row = $result->fetch_row()) {
        printf("<tr><td>%s</td><td>%s</td></tr>\n", $row[0], $row[1]);
    }
    printf("</table>\n");
    printf("</div>\n");
    $result->close();
}

// 切断
$mysqli->close();
?>
</div>

</body>
</html>

結果はご覧の通り。見事に化けました。でも、ヘンですよねえ。MySQLの環境はクライアントもサーバーも文字コードセットはUTF8なのに・・・。

あれ？「Current character set ...」の部分がlatin1になっているぞ。文字化けの原因はこれらしいが、何故latin1なの？

因みに「Current character set ...」は、

<?
// サーバーのデフォルト文字コードセットを取得
$charset = $mysqli->character_set_name();
printf("<b>Current character set</b> is <b style='color:#00f'>%s</b><br>\n", $charset);
?>

での結果で、PHPマニュアルでは「データベース接続のデフォルトの文字コードセット」とある。ならばUTF8ではないのかな？

もう少し調べてみると、PHPはMySQLの定義ファイル(my.ini, my.cnf)でクライアントやサーバーの文字コードを設定しても、PHPのビルド時に指定した文字コードセットが活きる、ということ。Linux系なら、ソースからコンパイルするので、そこら辺は対処（必然的に）するが、今回はWindows。バカちょんでインストしているので、デフォルトのlatin1が活きている模様。

そうかあ・・・忘れてたなあ。いつもはLinuxにMySQL乗っけて使うから。（単なるごまかしです・・・）

で、どーする？？

対処１：環境周りで対処

まずは、環境周りから攻めてみる。冒頭の参考サイトを見るとmy.iniやコマンド・オプションに次のようなものがあるというのが分かった。

my.ini

skip-character-set-client-handshake

コマンド・オプション

--skip-character-set-client-handshake

MySQLリファレンス（コマンド・オプション）を見ると、「クライアント情報を無視して、サーバーのデフォルトのキャラクタセットを使用する」とある。この逆が「--character-set-client-handshake」らしい。サイト「ぱんぴーまっしぐら」にはMySQL4.1.15以降、5.0.13以降で追加された、とある。

早速、試してみる。my.iniに追加。

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

MySQLを再起動して、PHPを再実行してみる。

うーーん、キレイだ。ちゃんと出るようになったゾ。

対処２：アプリ側で対処

ちゃんと出るようにはなったけど、もしレンタルサーバーみたいな、設定情報を変更できないような環境の場合では、先の対処では・・・。じゃあアプリで対処する方法なら、どんなものがあるのか？

PHPマニュアルを調べると、MySQLiに定義されているメソッドに「クライアントのデフォルト文字セットを設定する」ものがあるのを見つけた。試してみることにする。実際には色々な工夫があると思うが、とりあえずは固定でUTF8に変更するようにする。以下に変更する部分のコードだけを示す。先のコードと合わせてみて欲しい。

<?
・・・
// サーバーのデフォルト文字コードセットを取得
$charset = $mysqli->character_set_name();
printf("<b>Current character set</b> is <b style='color:#00f'>%s</b><br>\n", $charset);

/* クライアントのデフォルト文字セットを設定 */
if (!$mysqli->set_charset("utf8")) {
    printf("Failure loading character set utf8: %s<br>\n", $mysqli->error);
} else {
    printf("<b>Current character set</b> is <b style='color:#00f'>%s</b><br>\n", $mysqli->character_set_name());
}

// 一覧表示
・・・
?>

先ほどのmy.iniの設定をコメントにして設定内容を無効にする。

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

MySQLを再起動して、PHPを実行してみる。

ちゃんと出たね。「Current character set is ...」の２行目はちゃんとUTF8と出ているね。設定が上手くいった証拠だね。このほかに考えられる方法は、JavaのString.getBytes()メソッドみたいな方法をPHPでもやっちゃう、というのかな。でもデータ量が多くなったとき、大変かもね。

多分、この方法がいいんじゃあないかな。結構、勉強になったなあ・・・。

結論

PHPでは、クライアントのデフォルト文字コードセットを無視する方法を、

　１．アプリケーションレベル

　２．環境レベル

の順で検討／対処するようにするべき。これはJavaと比較して環境に左右されやすいためである。

*1：使用に際しての前提として、全体設定で文字コード=UTF8を設定しておくこと。

コメントを書く

tetsuya_odaka 2009/05/19 15:21 odakaです。
完璧です！！解決しました。
$mysqli->set_charset("utf8")の部分は、

$utf8_sql="SET NAMES UTF-8";
$conn->query($utf8_sql);

（$connはmysqliから取得したコネクション）
でも大丈夫そうですね。

Latinかぁ。

takuji_mezawa 2009/05/19 16:23 了解です。良かったです。
そうですね。

$utf8_sql="SET NAMES UTF-8";
$conn->query($utf8_sql);

でもOKです。

tetsuya_odaka 2009/05/20 15:36 インターネットを調べたところ、「SET NAMESするな」みたいなスレッドがたくさんありました。脆弱性の問題だとか。DAOレイヤーで使う限りにおいては、問題なかろうかと思うのですが...
また、MySQL5.1.34(community)、php5.2.9に上げたところ、SET NAMESだけだと効きません...
client-handshakeの設定をすれば、文字化けはおきずOKでした（だけど、これだと困る場合もありますよね）。

それと、mysqliオブジェクトではなくて、PEAR::MDB2を使いたいので探したところ、以下のサイトが参考になりました。
http://www.stbbs.net/blog/2008/08/mysql5pearmdb2utf-8.html

Java風にDSNに指定するだけ、ということ。

MySQL5.1.34(community)、php5.2.9(付属のmysqli.dll使用)、MDB2(2.5b)で確認が取れました。
MySQLのバージョンアップに伴う日本語ドタバタ劇は、いい加減にしたいなぁ、と思う今日この頃です。

tetsuya_odaka 2009/05/20 15:38 すみません追記です。

MySQL5.1.33(community)、php5.2.6(付属のmysqli.dll使用)、MDB2(2.5b)でも確認が取れました。

takuji_mezawa 2009/05/20 23:55 了解しました。
情報ありがとうございます。

なるほど・・・

トラックバック - http://d.hatena.ne.jp/takuji_mezawa/20090519/1242710031

リンク元

		2009/05
日	月	火	水	木	金	土
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31