code up

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

MySQLで文字化けしないFUNCTION作り

MySQL日本語の旅さんMySQL5開拓団さんのサイトを参考に自分に合ったFUNCTION作りの方法を模索した。

PROCEDURE, VIEW, TRIGGERでも応用できそう。

環境: サーバーはMySQL 5.0.82, 5.5.21。クライアントはMySQL 5.0.82のみ。

きっかけ

今回この方法を調査したのは外注した開発ベンダから納品して頂いたSQL文のCREATE FUNCTION文の中に日本語が含まれていたからだ。しかもどうやって登録するかの説明書はナシという放置プレイ。

delimiter |
create function getMonthLabel(year int, month int)
returns varchar(20)
character set utf8
deterministic
return concat(year, '年', month, '月');
|
delimiter ;

自分流FUNCTIONの書き方、登録方法

結論から。日本語を含む文字列には_utf8イントロデューサをつける。そしてSQL文はUTF-8で保存した上で、MySQLクライアントにファイルを渡す形で読み込ませる。WindowsでもLinux上でも同じコマンドで利用できる。先ほどのSQL文は次のように変更した。

drop function if exists getMonthLabel;

delimiter |
create function getMonthLabel(year int, month int)
returns varchar(20)
character set utf8
deterministic
return concat(year, _utf8'年', month, _utf8'月');
|
delimiter ;
mysql -u root -proot -h remote_host --default-character-set=utf8 db_name < create-function.sql

これでJavaからでもWindowsクライアントからでも文字化けすることなく関数を実行できるようになった。

mysql> select getMonthLabel(2000, 12);
+-------------------------+
| getMonthLabel(2000, 12) |
+-------------------------+
| 2000年12月              |
+-------------------------+
1 row in set (0.00 sec)

細かい解説

つなぎたいのはWindowsのMySQLクライアント、あとはTomcat(つまり、Java/JDBC)、Linux上のMySQLクライアント。

まずはMySQLクライアントをWindowsにインストール。今回試した際(クライアントバージョン5.0.82)は、my.iniがない状態でインストールされたので次のようなmy.iniをC:\Program Files\MySQL\MySQL Server 5.0に作成。

[client]
default-character-set=sjis
[mysqldump]
default-character-set=utf8

Windows-31J(MS932)特有の文字を利用しているならsjisの行をdefault-character-set=cp932としてもよい。サーバー側(今回はWindows XP上に構築してある)はUTF-8で設定してある。

[mysqld]
default-character-set=utf8

_utf8'日本語'

まずは、イントロデューサ(introducer)を指定した理由。つけた場合とつけない場合で、MySQL 5.0系で結果が異なったからである。

テスト用に次のようなFUNCTIONを用意。UTF-8で保存してmysql -u root -proot -h remote_host --default-character-set=utf8 db_name < create-function.sqlとして実行。

my.iniでsjisを指定しているので--default-character-set=utf8で読み込ませるファイルの文字エンコーディングを指定している。これでUTF-8でFUNCTIONが登録されたはずである。

drop function if exists charCheck;

delimiter |

create function charCheck()
returns varchar(60)
character set utf8
deterministic

return concat(
	charset('い'), ',',
	'う', ',',
	charset(_utf8'え'), ',',
	_utf8'お'
);
|

delimiter ;

バグトラッカーは調べてないのだが、5.0.82ではイントロデューサがないと文字エンコーディングが変わってしまうという不都合があった。5.5.21では無くても平気だったので5.0.82時点の不具合のような気がする。

まずは5.0の結果。

Server version: 5.0.82-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select charCheck();
+------------------+
| charCheck()      |
+------------------+
| sjis,(文字化け),utf8,お |
+------------------+
1 row in set (0.00 sec)

UTF-8で取り込んだはずの"い"と"う"がShift_JIS(クライアントのdefault-character-setの値)になってしまった。_utf8を指定した"え"と"お"はUTF-8になっている。サーバー側はUTF-8だが、my.iniでdefault-character-set=sjisとしているため、きちんと変換(utf8 to sjis)されて"お"が表示されている。

続いて、5.5。

Server version: 5.5.21 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select charCheck();
+------------------+
| charCheck()      |
+------------------+
| utf8,う,utf8,お |
+------------------+
1 row in set (0.00 sec)

5.5ではどちらも(イントロデューサを指定しなかった文字も)UTF-8となっている。

5.5.21ではイントロデューサの有無によって結果が変わらないのに対して5.0.82では何故か関数の中身の文字コードが接続時の文字エンコーディングを参照しているようだ。よって先ほど書いた通り、5.0.82の不具合ではないかと考えている。本来は必要ないかもしれないが、現状5.0系も使用し続けなければいけないので本不具合に対応するため_utf8イントロデューサを付けることとした。

UTF-8でSQL文を保存する

Linuxの場合、PuTTYを設定すれば端末の送受信の文字コードをUTF-8にできる。SQuirreL SQL Clientを使った場合はJDBCを経由するのでデフォルトではサーバーの文字エンコーディング、つまり今回の場合はUTF-8となる。

しかしWindowsの場合コマンドプロンプトでUTF-8を入力することはできない(chcp 65001と入力することで表示させることはできるようだが・・・)。

実行するSQL文はどの環境(Win,Linux,JDBC)から実行しても同じ結果となって欲しい。よってLinuxだけであればmysqlクライアントのコンソールにSQL文をペーストすればいいかもしれないが、Windowsだと文字化けを引き起こす。

WindowsのコマンドプロンプトからCREATE FUNCTION文を直接実行した場合(コピーペーストとかで直接コマンドプロンプトウィンドウ上に入力)。下記は登録後の5.0の結果。5.5でも、エラーメッセージの\が\となるが同じエラーメッセージが出た。

mysql> select charCheck();
ERROR 1366 (HY000): Incorrect string value: '\x82\xA8,' for column 'charCheck()' at row 1

0x82A8はShift_JISの"お"、UTF-8だったら0xE3818Aとなるはず。つまり、Shift_JISの"お"がIncorrect string valueということだ。

select charset(charCheck());と実行すると'utf8'と返ってくるが、select hex(charCheck());としようとするとエラーとなる。charset()は情報関数(Information Functions)である。hex()は文字列関数となっており分類が異なっている。

どうやら文字コード変換前の状態を情報関数に渡すのに対して、文字列関数には文字コード変換後のデータを渡しているようだ。これにより関数charCheck()実行後の戻り値の変換処理あたりで問題があると推測できる。1366のエラーコードはサーバー側のエラーコードとのことなので、サーバー側で文字コードを変換(今回の場合はsjisに変換)し、文字列化しようとした際にエラーとなっているということだろう。

mysql> select charset(charCheck());
+----------------------+
| charset(charCheck()) |
+----------------------+
| utf8                 |
+----------------------+
1 row in set (0.00 sec)

mysql> select hex(charCheck());
ERROR 1366 (HY000): Incorrect string value: '\x82\xA8' for column 'charCheck' at row 1

_utf8イントロデューサの意味は、次のようになっている。

_charset_nameは形式上イントロデューサと呼ばれています。指定すると、「キャラクタセットXの文字列が後続する」ことがパーサに通知されます。上記はユーザの混乱を招いていたため、ここで強調しておきますが、イントロデューサは変換の原因にはならず、文字列の値が変更されないことを示すにすぎません。

・・・分かりにくいので英語で。

The _charset_name expression is formally called an introducer. It tells the parser, "the string that is about to follow uses character set X." Because this has confused people in the past, we emphasize that an introducer does not change the string to the introducer character set like CONVERT() would do. It does not change the string's value, although padding may occur.

・・・簡単に訳す。

_charset_name式は公式にはイントロデューサと呼ばれる。これは"後続の文字列が文字セットXである"事をパーサーに伝える。この事はかつてユーザーを混乱させていたため強調しておくが、イントロデューサはCONVERT()のように文字列を指定された文字セットに変換するものではない。イントロデューサはパディングをする事もあるが、文字列の値は変更しない。

『padding may occur/パディングをする事もある』というのはおそらく固定長の文字列カラムにデータを挿入する時にスペースがpaddingされる事を指しているのかも、未確認。

Windowsのコマンドプロンプト上からSQL文を実行した場合、そこに書かれた日本語は全てもれなくShift_JIS(Windows-31J)である。その中にあった_utf8'お'という箇所は0x82A8(=Shift_JISの"お")という文字コードがUTF-8文字であると指示したことになる。0x82A8はUTF-8には存在しない文字コードであるためIncorrect string valueとなった。つまり、_utf8の後続の文字が実際はShift_JISなのにUTF-8であると誤って指示したコードなのである。これは登録時にはエラーとして検出されず、実行時にエラーとなるようだ。

次のCREATE FUNCTION文のように16進数表記のUTF-8文字列を指定してもいいかもしれない、異なる環境から登録しても実行時に変な文字化けを起こしたりしないようだ。ただ、ひとつひとつの日本語を変換したりコードを調べるのは大変、スクリプトを書いたとしてもSQL文を変更する度にスクリプトの再実行することを忘れちゃいそうで怖い。

ということで、UTF-8で保存→それを<リダイレクト演算子で読み込むという方法を自分のやり方にすることにした。

drop function if exists charCheck;

delimiter |

create function charCheck()
returns varchar(60)
character set utf8
deterministic

return concat(
	charset(0xE38184), ',',
	0xE38186, ',',
	charset(_utf8 0xE38188), ',',
	_utf8 0xE3818A
);
|

delimiter ;

上記FUNCTIONの結果(5.0も5.5も同じ結果)。文字化けは5.0/5.5どちらでも発生しなかった。

mysql> select charCheck();
+-------------------+
| charCheck()       |
+-------------------+
| binary,う,utf8,お |
+-------------------+
1 row in set (0.00 sec)

--default-character-set=utf8

コマンドラインでデフォルト文字セット(default-character-set)を指定することで、my.ini/my.cnfに書かれた値を上書きする、つまり今回の場合はWindows用にsjis/cp932と指定されたものをutf8で上書きし、どんな環境(my.ini/my.cnfにあるdefault-character-setがいかなる値であろうとも)SQL文を確実にUTF-8エンコーディングで処理するようにしている。あるいはmy.ini/my.cnfが存在しなくても、utf8で処理するようになる。

以下はmy.iniが存在しない場合。<リダイレクト演算子によるUTF-8なSQL文の実行は--default-character-set=utf8で実行済み。5.5ではイントロデューサの有無は結果に影響しないが、5.0の場合、結果が状況により異なってしまう。

Server version: 5.0.82-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> charset sjis;
Charset changed
mysql> select charCheck();
+-----------------+
| charCheck()     |
+-----------------+
| sjis,(文字化け),utf8,お |
+-----------------+
1 row in set (0.00 sec)

charsetコマンド(クライアントの文字セットを切り替えるコマンド)を関数を実行してから行っても反映されない。DETERMINISTICが効いてるからかな。

Server version: 5.0.82-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select charCheck();
+-------------------+
| charCheck()       |
+-------------------+
| latin1,(文字化け)utf8,? |
+-------------------+
1 row in set (0.00 sec)

mysql> charset sjis;
Charset changed
mysql> select charCheck();
+---------------------+
| charCheck()         |
+---------------------+
| latin1,??(文字化け),utf8,お |
+---------------------+
1 row in set (0.00 sec)

5.5。

Server version: 5.5.21 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select charCheck();
+---------------+
| charCheck()   |
+---------------+
| utf8,?,utf8,? |
+---------------+
1 row in set (0.00 sec)

mysql> charset sjis;
Charset changed
mysql> select charCheck();
+-----------------+
| charCheck()     |
+-----------------+
| utf8,う,utf8,お |
+-----------------+
1 row in set (0.00 sec)
関連記事
タグ:MySQL
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。