UTF-8 エンコードされた絵文字を MySQL / JDBC で取り扱うには？

2013.06.08 追記：JDBC 接続文字列で characterEncoding / connectionCollation を指定すると思った通りの挙動をしてくれないようなので、別の方法 (多分これが正しい方法) を記載しました。

Unicode における、こんな 感じの絵文字、いわゆる Unicode の 追加面 の文字、Java で言えばサロゲートペアでの表現が必要となる文字を、JDBC 経由で UTF-8 エンコーディングして MySQL のテーブル・カラムに格納しようとすると、以下の例外が発生することがあります。

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x81 h...' for column 'col_name' at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)

Twitter API 叩いて得たツイートを MySQL に入れて蓄積したい、みたいなことをしようとして、毎回このスタックトレースと格闘しているので、対応方法を忘れないようにメモしておきます。

確認した環境は以下のとおり。

• MySQL 5.5.25a
• MySQL Connector/J 5.1.22
• Java 1.7.0_07-b10

すべきこと

• 強く推奨される対処方法
  1. my.cnf (Windows の場合は my.ini) の [mysqld] セクションで character-set-server に utf8mb4 を設定する
  2. 既存のデータベース / テーブル / カラムの CHARSET を確認する
• いかんともし難い理由により my.cnf を変更できない場合の代替手段
  1. データベース / テーブルの CHARSET に utf8mb4 を指定する
  2. Java のプログラムから具体的な SQL を発行する前に SET NAMES utf8mb4 を発行する

ざっくりとまとめると上記のとおり、2 つの手段があります。以下でそれぞれ説明します。

強く推奨される対処方法

my.cnf (my.ini) を変更することができるならば、こちらの手段での対処を強くおすすめします。こちらの方法であれば、Java プログラム側は特別な対処をする必要がありません (JDBC 接続文字列での characterEncoding や connectionCollation の設定も不要です)。

1. my.cnf の [mysqld] セクションで character-set-server に utf8mb4 を設定する

以下のように、character-set-server の値を utf8mb4 に書き換えるだけです。

(略)

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# server_type=3
[mysqld]

(略)

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8mb4  # ← ここを utf8mb4 にする

2. 既存のテーブル / カラムの CHARSET を確認す

続いて、すでに存在するテーブルやカラムの CHARSET を以下のクエリで、テーブルごとに確認します。

SHOW CREATE TABLE テーブル名

このクエリを実行した結果、たとえば、

CREATE TABLE `tbl_name` (
  `col_name` varchar(8) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAUTL CHARSET=utf8bm4;

このような結果が得られた場合は、tbl_name CHARSET は utf8mb4 だけれども、col_name は utf8 であることが分かります。このテーブルのカラムをすべて utf8mb4 の CHARSET にしたい場合は、

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4;

というクエリを発行します。この確認 (と CHARSET 変更) を、追加面の文字が格納される可能性のあるすべてのテーブルに対して実施します (確認が面倒どうであれば、すべてのテーブルに対して無差別に ALTER TABLE ～ してしまってもいいかもしれませんね)。

いかんともし難い理由により my.cnf を変更できない場合の代替手段

こちらは my.cnf を変更出来ない場合の対処方法です。理由は後述しますが、こちらの対応方法はあまりおすすめできません。

1. データベース / テーブルの CHARSET に utf8mb4 を指定する

この対応方法はまず、データベース or テーブルで文字列を取り扱う際の CHARSET に utf8mb4 を指定することから始まります。

データベース全体の CHARSET を設定するのであれば、CREATE DATABASE でデータベースを作成する際に以下のように指定するとよいでしょう。

CREATE DATABASE
    db_name
  DEFAULT CHARSET
    utf8mb4;

また、テーブルを作成するときに、テーブル単位 or カラム単位で CHARSET を指定することもできます。追加面の文字が入るテーブル / カラムのみ変更したい場合は、こちらの指定がよいでしょう。

/* テーブル単位で指定する場合 */
CREATE TABLE tbl_name (
  col_name VARCHAR(100)
) ENGINE 
    InnoDB
  CHARSET
    utf8mb4;

/* カラム単位で指定する場合 */
CREATE TABLE tbl_name (
  col_name VARCHAR(100) CHARSET utf8mb4
);

2. Java のプログラムから具体的な SQL を発行する前に SET NAMES utf8mb4 を発行する

続いて Java プログラム側の話になります。

INSERT / UPDATE などの具体的な SQL を発行する前に、「コネクション単位で」SET NAMES utf8mb4 のクエリを発行するようにします。つまり以下のプログラムのように、java.sql.Connection オブジェクトを取得した直後に java.sql.PreparedStatement オブジェクトなどを利用して SET NAMES utf8mb4 を発行するようにします。

try (Connection conn = 
         DriverManager.getConnection("jdbc:mysql://localhost/dbname?user=u&password=p")) {
    try (PreparedStatement pstmt = conn.prepareStatement("SET NAMES utf8mb4");
         ResultSet rset = pstmt.executeQuery()) {
    }

    // この後に具体的な SQL を発行する...
}

SET NAMES utf8mb4 の発行結果はコネクションをまたいで引き継がれるわけではないので、コネクションを取得し直す度に毎回 発行し直す必要があります (逆を言えば、同一のコネクション内であれば、再発行する必要はありません)。

この対応方法がおすすめ出来ない理由

MySQL Connector/J のマニュアル 22.3.5.4. Using Character Sets and Unicode を読み進めて見ると分かりますが、

上記のキャプチャのように、「SET NAMES を JDBC のコネクション上で発行すること自体すべきでない」と明記されています。 加えて O/R マッパーを利用する場合、java.sql.Connection オブジェクトの管理が O/R マッパー任せとなるので、いつ SET NAMES utf8mb4 を発行すればよいか、そのタイミングを図るのが非常に難しくなります。

特別な理由がない限りは、my.cnf の設定を変更する方法を利用し、「どうしても my.cnf は触れない／触りたくないんだ…！」というときのみ、こちらの利用をするとよいかと思います。

2013.06.08 削除：以下の対処は不要で、文字数分の大きさの VARCHAR カラムをすれば十分でした。

2. Unicode 追加面 が格納される可能性のある VARCHAR のカラムを、文字数の４倍に設定する

Unicode 追加面の文字を VARCHAR のカラムに格納すると、同文字１文字あたり VARCHAR 4 文字分を消費するようです。そのため、例えば 100 文字格納できるカラムが合った場合に、すべての文字が Unicode 追加面の文字である 100 文字の文字列を格納することを想定して、VARCHAR のカラムの文字数を 400 に設定する必要があります。

つまり、

CREATE TABLE tbl_name (
  col_name VARCHAR(100)
)

のようなテーブルがあったとして、col_name のカラムに実際に 100 文字格納したいんだけど、Unicode 追加面の文字が混じる可能性があるのであれば、

CREATE TABLE tbl_name (
  col_name VARCHAR(400)
)

のようにしましょう、ということです。

2013.06.08 削除：この対処も不要で、特に接続文字列に何かを付け加える必要はありません。

3. JDBC の接続文字列で characterEncoding / connectionCollation を指定する

MySQL のサーバ側の設定などは上記までで、残すは JDBC 周りとなります。JDBC 周りの設定に手を入れない限りは、最初に示したスタックトレースとおさらばすることはできません。

JDBC 周りの設定内容は以下のとおりです。

• characterEncoding ... UTF-8
• connectionCollation ... utf8mb4_general_ci

JDBC の接続文字列での指定例は以下になります

jdbc:mysql://host:3306/db_name?characterEncoding=UTF-8&connectionCollation=utf8mb4_general_ci

以上。