MYSQL 4.1 の余計なエスケープ処理

質問

おはようございます。PERL 5.8.5 からMYSQL4.1 サーバーへのデータINSERT 時の文字変換処理について教えてください。

MYSQL 4.1 の文字コードは次の通りです。

SHOW VARIABLES LIKE 'character?_set?_%'; の結果

character_set_client ：binary
character_set_connection：binary
character_set_database：utf8
character_set_results：binary
character_set_server：utf8
character_set_system：utf8

MYSQL 4.1 サーバーが上記のような状態で、PERL で次の処理をします。なお、PERL ソース自体はEUC で書かれています。

#!/usr/bin/perl

use Jcode;
use DBI;

$string = "あああ";
Jcode::convert( ?$string, 'utf8', 'euc' );

$dbh = DBI->connect( 'DBI:mysql:DB名:ホスト名:3306', 'ユーザー名', 'パスワード' ,{AutoCommit => 0} );

$sth = $dbh->prepare( "set names utf8" );
$sth->execute;
$sth->finish;

$sth = $dbh->prepare( "insert into test values( ? )" );
$sth->bind_param( 1, $string );
$sth->execute;
$sth->finish;

$dbh->commit;
$dbh->disconnect;

上記のソースを実行すると、MYSQL 4.1 サーバーには [あ?あ?あ] のような、余計なエスケープが掛かった状態で文字がINSERT されているのです。

私としては、set names utf8 で、MYSQL 4.1 サーバーとクライアントで同様の文字コードを使うようにしているので、MYSQL 4.1 における余計な文字コード変換はないものと思っています。余計な文字コード変換がないにもかかわらずエスケープ文字が入る理由が分かりません。

この現象についてお分かりの方がいらっしゃいましたら教えて頂きたく思います。なお、ネットでも調べたのですが、どの方も未解決でしたので質問さえて頂きました。

お願いします。

回答No.4

onosuke

>set names statememt のSQL 文が、MYSQL Client Library
>にどのように影響を与えているかという部分が理解できません。
逆です。set names statememt のSQL 文が影響を与えることができない為に発生する問題です。

そのため、MYSQL Client Libraryでは、
"set names"SQL文を利用せず、
　mysql_set_character_set()関数を利用すること
と決められています。

これはSQLの文字列リテラルにエスケープ文字の挿入を行う関数
mysql_real_escape_string()
に現在使用している character set を教える必要がある為です。

さらに、「何故character setを教える必要があるのか」と言うと、
エスケープ処理が必要な文字が character set毎に異なる為です。

以上は、「何故？」という観点での記述でしたが、
「ルール」という観点で記述すると以下のようになります。
・SQL中の文字列リテラルにはエスケープ処理が必要。
・エスケープ処理は character set毎に異なる処理が必要。
・エスケープ処理は、クライアント内部で行う必要がある。
・mysql_real_escape_string()関数を利用すると、クライアント内部で行う必要のあるエスケープ処理をcharacter set毎に決められたルールで行うことができる。
・mysql_real_escape_string()関数が利用する character set情報は、設定ファイルで事前設定しておくか、mysql_set_character_set()関数で設定することが必要。
・どちらの設定方法もSQL server側には、"set names"SQLを発行したのと同じ効果がある。

お礼

ご返事ありがとうございます。

上記説明で、納得できました。set names 文が Mysql Client Library に影響を与えることができないため、余計なエスケープ処理が走るということですね。

確かにPERL DBI で接続する分には、mysql_set_character_set() の存在は意識しません。まず、C のAPI から勉強すべきだったかもしれませんね。

余談ですが、そもそも今回質問させて頂いたのは、MT 3.3 をMysql 4.1.15 で利用するときに不都合が起きたからです。MT 3.3 の説明通りの設定をMysql 4.1 に設定したにも関わらず、日本語が正常に入力できませんでした。最終的にMysql 4.1.21 にアップグレードすることで今回は対応しましたが、なぜこのような現象になるのかが気持ち悪かったのです。

set names 文はMysql 特有のSQL だと思いますが、まさかMySQL Client Library に問題があるとは思ってもいませんでした。

今回は非常に勉強させて頂きました。ありがとうございました。

回答No.3

onosuke

クライアント側の文字コードをEUCに統一した方が良いというのは

> utf8 にエンコードしたデータを
というコードを書く労力が必要。→バグの元

>set names utf8
>でMYSQL 4.1 に流しているので問題ないかと思いますが
という誤りが後を絶たない、

という理由。

「Perl側で細かいUNICODEマッピング制御を行う為に必要」
「実はCGIでブラウザがUTF8でデータを送ってくる（今回はテスト）」
といった場合は別ですが、そうでなければ、
MySQLサーバにコード変換をやらせる方が楽ですよ。

回答No.2

onosuke

まぁ、
Perlしか叩かない人には、理解する機会すら無い事象なので、
食って掛かりたくなるんでしょうね。

今回の根本原因は、SET NAMES statementで指定した情報が、
MySQL client library関数に反映されない為。

これは、言い換えると
クライアントからサーバへ送信後、サーバ上で解釈される SQL queryが
クライアント側内部動作であるMySQL client library関数に
影響を与えることができない為。

Perl DBI経由のアクセスは、MySQL client library関数を内部で利用しています。その為、SQL的に正しい操作であっても、Perl DBI的に誤りとなるケースがあるんですよ。

お礼

ご回答ありがとうございます。適切なご回答頂き感謝しております。

DBI からMYSQL クライアントライブラリが呼ばれていることは理解できますが、set names statememt のSQL 文が、MYSQL Client Library にどのように影響を与えているかという部分が理解できません。

set names statement はクライアント文字コードをサーバーに伝えるためのSQL 文と認識しております。そして、MYSQL による文字コード変換はサーバー上で行われるものと思っています。サーバー上で文字コード変換が行われるのであれば、MYSQL Client Library はMYSQL サーバーに対して「クライアントの文字コード」と「クライアントの文字コードで構成されたデータ(SQL も含む)」を渡せば良いので、set names statement が MYSQL Client Library に影響することはないだろうと思っているのです。

初心者なので的外れな質問かとは思いますが、よろしければ教えてください。お願いします。

回答No.1

onosuke

set namesの利用自体に問題がある可能性を考えましたか？

文字コードの設定には、my.cnfオプションファイルを利用してください。

DBI:connect($dsn,....)で指定する
　$dsnのDataSourceオプションで利用するmy.cnfファイルの制御が行えます。

例：DBI:connect
$dbi->connect($dsn.";mysql_read_default_file=/anywhere/my.cnf;mysql_read_default_group=perl_client", ....)

例：my.cnf
[perl_client]
default_character_set=XXXXX

# 根本の話としてperlソースがEUCならば、
# clientのcharacter_setもujis(EUC)にすべきでは？

お礼

ご回答ありがとうございます。

確かに、おっしゃる通り
$dbi->connect($dsn.";mysql_read_default_file=/anywhere/my.cnf;mysql_read_default_group=perl_client", ....)
のような指定でMySQL 4.1 に接続すると余計なエスケープ処理がされないようです。ただ、クライアントのエンコードをDBI:connect で指定する場合も、set names で指定する場合も同じと考えていますが違いますか？

また、「set namesの利用自体に問題」ということですが、どのような点が問題なのでしょうか？PERL のソース自体は確かにEUC-JP ですが、utf8 にエンコードしたデータを set names utf8 でMYSQL 4.1 に流しているので問題ないかと思いますが、この認識は間違っていますか？

お願いします。