HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

MySQLで4バイトのUTF-8文字を扱ってみる

JIS X 0213:2004で追加された「叱」の異体字は、Unicodeにおいては「UTF-8で符号化すると4バイトになる」コードが割り当てられている。また、中国語をまともに扱おうと思ったら「UTF-8で4バイト」の文字を扱う必要が出てくる。
そんな文字どもをMySQLデータベースに放り込もうと、UTF-8指定でがんばっていたのだが、ひとつの結論に行き着いたらしい。

UTF-8で4バイト」の文字を扱おうと思ったら、MySQL 5.5.3以降を使い、さらに"utf8mb4"を指定せよ、という冗談のような本当の話。

まぁグダグダ言っても始まらないので、まずは手元にある環境で試してみるところから。

事前準備

環境として、以下のものを使用する。

まずはコマンドラインクライアントで以下のデータベースを作成する。

  • 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(
    '', //従来から定義されている文字
    '&#134047;', //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 &#134047; [    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のマニュアルには以下のように書いてある。


注意:

この関数は、MySQL 5.0.7 以降でないと使用できません。

注意:

文字セットを変更するにはこの方法を使うことを推奨します。 mysql_query() で SET NAMES .. を実行する方法はお勧めできません。

で、調べてみたところ「SET NAMES 'x'」の他に「SET CHARACTER SET x」というのもあるらしい。(http://dev.mysql.com/doc/refman/5.1/ja/charset-connection.html)
で、ちゃんと検証しないのも気持ち悪いので、ちょっと調べてみた。
以下のクエリを発行すると、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」でも大勢に影響はない。が、実は微妙な違いがあるんだけどここでは割愛)。