JIS X 0213:2004で追加された「叱」の異体字は、Unicodeにおいては「UTF-8で符号化すると4バイトになる」コードが割り当てられている。また、中国語をまともに扱おうと思ったら「UTF-8で4バイト」の文字を扱う必要が出てくる。
そんな文字どもをMySQLデータベースに放り込もうと、UTF-8指定でがんばっていたのだが、ひとつの結論に行き着いたらしい。
「UTF-8で4バイト」の文字を扱おうと思ったら、MySQL 5.5.3以降を使い、さらに"utf8mb4"を指定せよ、という冗談のような本当の話。
まぁグダグダ言っても始まらないので、まずは手元にある環境で試してみるところから。
事前準備
環境として、以下のものを使用する。
- OS
- Windows Vista Business SP2 (x86)
- PHP
- v5.3.3 (MSVC6版)
- mysqlnd 5.0.7-dev - 091210 - $Revision: 300533 $
- MySQL
- Server version: 5.1.32-community MySQL Community Server
- Server version: 5.5.11 MySQL Community Server
まずはコマンドラインクライアントで以下のデータベースを作成する。
- sandbox_utf8 (MySQL v5.1とv5.5で共通)
mysql> CREATE DATABASE sandbox_utf8 DEFAULT CHARSET utf8; Query OK, 1 row affected (0.06 sec) mysql> USE sandbox_utf8; Database changed mysql> CREATE TABLE hoge(idx int, str varchar(64) binary); Query OK, 0 rows affected (0.13 sec) mysql>
- sandbox_utf8mb4 (MySQL v5.5のみ)
mysql> CREATE DATABASE sandbox_utf8mb4 DEFAULT CHARSET utf8mb4; Query OK, 1 row affected (0.01 sec) mysql> USE sandbox_utf8mb4; Database changed mysql> CREATE TABLE hoge(idx int, str varchar(64) binary); Query OK, 0 rows affected (0.09 sec) mysql>
- (参考)MySQL v5.1でutf8mb4
mysql> CREATE DATABASE sandbox_utf8mb4 DEFAULT CHARSET utf8mb4; ERROR 1115 (42000): Unknown character set: 'utf8mb4' mysql>
事前のうわさどおり、v5.1では"utf8mb4"なんてものはそもそも存在しない。
ちなみに、各バージョンでサポートされているcharacter setの一覧。
実際にデータを放り込んでみる
以下のようなプログラムを実行してみる。
ちょっと読むのが面倒なのでざっと説明しておくと、指定したDBサーバー上の指定したDBに対して、character setをDBサーバーに教えた上で指定した文字列を放り込む、というプログラム。
なお、以下の中で「𠮟」と書かれた部分は「叱」の異体字だと思って読んでもらえれば、と。
<html> <head> <meta charset="UTF-8" /> <title>char_enc.php</title> </head> <body> <pre><?php function test_mysql_utf8($dbServer, $database, $encoding, $chars) { $conn = mysql_connect( $dbServer['host'] . ($dbServer['port'] > 0 ? ':' . $dbServer['port'] : ''), $dbServer['user'], $dbServer['password']); if ($conn) { if (mysql_select_db($database, $conn)) { mysql_query("SET NAMES '{$encoding}'", $conn); mysql_query("DELETE FROM hoge", $conn); foreach ($chars as $idx => $ch) { mysql_query("INSERT INTO hoge(idx, str) VALUES({$idx}, '{$ch}')"); } $res = mysql_query("SELECT idx, str FROM hoge ORDER BY idx"); if ($res) { while (($row = mysql_fetch_assoc($res))) { printf("%5d %s [%5d]\n", $row['idx'], $row['str'], strlen($row['str'])); } mysql_free_result($res); } } mysql_close($conn); } } $chars = array( '叱', //従来から定義されている文字 '𠮟', //JIS X 0213:2004 で追加された、UTF-8で4バイトになる文字 ); $encodings = array( 'utf8', 'utf8mb4', ); $dbServers = array( 'MySQL v5.1' => array( 'user' => 'root', 'password' => 'admin', 'host' => 'localhost', 'port' => 3306, 'databases' => array('sandbox_utf8'), ), 'MySQL v5.5' => array( 'user' => 'root', 'password' => 'admin', 'host' => 'localhost', 'port' => 3309, 'databases' => array('sandbox_utf8', 'sandbox_utf8mb4'), ), ); foreach ($dbServers as $dbName => $dbServer) { printf("=== %s: %s:%d ===\n", $dbName, $dbServer['host'], $dbServer['port']); foreach ($dbServer['databases'] as $database) { printf("=== %s ===\n", $database); foreach ($encodings as $encoding) { printf("=== %s ===\n", $encoding); test_mysql_utf8($dbServer, $database, $encoding, $chars); } } printf("\n"); } ?></pre> </body> </html>
これを実行すると、次のような出力を得られる。
=== MySQL v5.1: localhost:3306 ===
=== sandbox_utf8 ===
=== utf8 ===
0 叱 [ 3]
=== utf8mb4 ===
0 叱 [ 3]
=== MySQL v5.5: localhost:3309 ===
=== sandbox_utf8 ===
=== utf8 ===
0 叱 [ 3]
=== utf8mb4 ===
0 叱 [ 3]
=== sandbox_utf8mb4 ===
=== utf8 ===
0 叱 [ 3]
=== utf8mb4 ===
0 叱 [ 3]
1 𠮟 [ 4]この結果から、以下の3条件が全て必須であることが分かる。
- MySQL v5.5(v5.5.3以降)を使用すること
- DB作成時(あるいはテーブル作成時)に"utf8mb4"を明示すること
- クエリ文字列やクエリの結果を"utf8mb4"で扱うことを明示すること
character setの指定
先の検証では、実はさらっと無視していたのだが、PHPのマニュアルどおりにしているとはまるポイントが1つある。
上記のプログラムの以下の部分：
mysql_query("SET NAMES '{$encoding}'", $conn);
を、以下の形：
mysql_set_charset($encoding, $conn);
に書き換えると、先の結果とは違って4バイトのUTF-8文字が一切挿入されなくなる。
そもそもPHPのマニュアルには以下のように書いてある。
で、調べてみたところ「SET NAMES 'x'」の他に「SET CHARACTER SET x」というのもあるらしい。(http://dev.mysql.com/doc/refman/5.1/ja/charset-connection.html)
注意:この関数は、MySQL 5.0.7 以降でないと使用できません。
注意:
文字セットを変更するにはこの方法を使うことを推奨します。 mysql_query() で SET NAMES .. を実行する方法はお勧めできません。
で、ちゃんと検証しないのも気持ち悪いので、ちょっと調べてみた。
以下のクエリを発行すると、character setなどを確認できるらしい。
SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%';
また、PHPでクライアントエンコーディングを取得するのに「mysql_client_encoding()」という関数がある。
ということで、「mysql_set_charset()」「SET NAMES」「SET CHARACTER SET」それぞれを実行したときに、これらのクエリの結果と「mysql_client_encoding()」の返り値がどのように変わるかを見てみる。
まずはプログラム。
<html> <head> <meta charset="UTF-8" /> <title>char_enc.php</title> </head> <body> <pre><?php function debug_query($sql, $conn, $clientEncoding) { $clientEncoding = ($clientEncoding == "utf8mb4" ? "utf8" : $clientEncoding); $res = mysql_query($sql); if ($res) { printf("--------\n"); while (($row = mysql_fetch_assoc($res))) { foreach ($row as $val) { printf(" %-25s", mb_convert_encoding($val, 'UTF-8', $clientEncoding)); } printf("\n"); } printf("--------\n"); mysql_free_result($res); } } /** * @param $mode クライアントの文字エンコーディング x を指定する方法 * 1: mysql_set_charset('x', $conn) * 2: SET NAMES 'x' * 3: SET CHARACTER SET x */ function test_mysql_charset($dbServer, $database, $encoding, $mode = 0) { $conn = mysql_connect( $dbServer['host'] . ($dbServer['port'] > 0 ? ':' . $dbServer['port'] : ''), $dbServer['user'], $dbServer['password']); if ($conn) { if (mysql_select_db($database, $conn)) { debug_query("SHOW VARIABLES LIKE 'character_set%'", $conn, $encoding); debug_query("SHOW VARIABLES LIKE 'collation%'", $conn, $encoding); printf("mysql_client_encoding = %s\n", mysql_client_encoding($conn)); //試しに全然関係ないcharsetを指定しておく if ($mode == 1) { mysql_set_charset('ujis', $conn); } if ($mode == 2) { mysql_query("SET NAMES 'ujis'", $conn); } if ($mode == 3) { mysql_query("SET CHARACTER SET ujis", $conn); } debug_query("SHOW VARIABLES LIKE 'character_set%'", $conn, $encoding); debug_query("SHOW VARIABLES LIKE 'collation%'", $conn, $encoding); printf("mysql_client_encoding = %s\n", mysql_client_encoding($conn)); if ($mode == 1) { mysql_set_charset($encoding, $conn); } if ($mode == 2) { mysql_query("SET NAMES '{$encoding}'", $conn); } if ($mode == 3) { mysql_query("SET CHARACTER SET {$encoding}", $conn); } debug_query("SHOW VARIABLES LIKE 'character_set%'", $conn, $encoding); debug_query("SHOW VARIABLES LIKE 'collation%'", $conn, $encoding); printf("mysql_client_encoding = %s\n", mysql_client_encoding($conn)); } mysql_close($conn); } } $encodings = array( 'sjis', 'utf8', 'utf8mb4', ); $database = 'sandbox_utf8mb4'; $dbServer = array( 'user' => 'root', 'password' => 'admin', 'host' => 'localhost', 'port' => 3309, ); printf("=== %s:%d/%s ===\n", $dbServer['host'], $dbServer['port'], $database); foreach ($encodings as $encoding) { foreach (array(1, 2, 3) as $mode) { printf("=== %s:%d ===\n", $encoding, $mode); test_mysql_charset($dbServer, $database, $encoding, $mode); } } ?></pre> </body> </html>
このプログラムの実行結果を表にまとめると次のようになる。全部を載せると長くなるので、2つの点に着目したまとめにする。(上記のデータベースsandbox_utf8mb4を使用した)
- "ujis"を指定した後に"sjis"を指定した場合の結果
Variable_name mysql_set_charset() SET NAMES SET CHARACTER SET ---- ---- ---- ---- character_set_client sjis sjis sjis character_set_connection sjis sjis utf8mb4 character_set_database utf8mb4 utf8mb4 utf8mb4 character_set_filesystem binary binary binary character_set_results sjis sjis sjis character_set_server utf8 utf8 utf8 character_set_system utf8 utf8 utf8 collation_connection sjis_japanese_ci sjis_japanese_ci utf8mb4_general_ci collation_database utf8mb4_general_ci utf8mb4_general_ci utf8mb4_general_ci collation_server utf8_general_ci utf8_general_ci utf8_general_ci ---- ---- ---- ---- mysql_client_encoding() sjis utf8 utf8
「mysql_client_encoding()」が影響を受けるという1点を除いては、「mysql_set_charset()」と「SET NAMES」は同じ動作をする。
一方、「SET CHARACTER SET」は「xxx_connection」に対する設定値のみが他と異なる。
詳しくはマニュアルを読めばきっと分かると思う(http://dev.mysql.com/doc/refman/5.1/ja/charset-connection.html)。
- "ujis"を指定した後に"utf8mb4"を指定した場合の結果
Variable_name mysql_set_charset() SET NAMES SET CHARACTER SET ---- ---- ---- ---- character_set_client ujis utf8mb4 utf8mb4 character_set_connection ujis utf8mb4 utf8mb4 character_set_database utf8mb4 utf8mb4 utf8mb4 character_set_filesystem binary binary binary character_set_results ujis utf8mb4 utf8mb4 character_set_server utf8 utf8 utf8 character_set_system utf8 utf8 utf8 collation_connection ujis_japanese_ci utf8mb4_general_ci utf8mb4_general_ci collation_database utf8mb4_general_ci utf8mb4_general_ci utf8mb4_general_ci collation_server utf8_general_ci utf8_general_ci utf8_general_ci ---- ---- ---- ---- mysql_client_encoding() ujis utf8 utf8
"sjis"の場合と同じ結果になるかと思いきや、「mysql_set_charset()」の場合は"utf8mb4"が届いていない。「mysql_client_encoding()」の結果にも反映されていないところを見ると、ドライバの中ではじかれているのだろうか。
そんなわけで、"utf8mb4"の場合には相変わらず「SET NAMES」を使わざるを得ない(または「SET CHARACTER SET」でも大勢に影響はない。が、実は微妙な違いがあるんだけどここでは割愛)。
