MySQLのutf8はデフォルトで絵文字を取り扱うことが出来ません。MySQL5.5以降ならば対応する方法があります。
環境
$ cat /etc/redhat-release
CentOS release 6.5 (Final)
$ mysql -V
mysql Ver 14.14 Distrib 5.5.48, for Linux (x86_64) using readline 5.1
絵文字を挿入する
文字コードがutf8に設定されたMySQL5.5環境下で、下記クエリを実行してみます。
CREATE DATABASE sushiya;
USE sushiya;
CREATE TABLE sushi(
id INT AUTO_INCREMENT,
name VARCHAR(20),
INDEX(id)
);
INSERT INTO sushi(name) VALUES("タマゴスシ");
INSERT INTO sushi(name) VALUES("マグロ🍣スシ");
INSERT INTO sushi(name) VALUES("マグロ🐟スシ");
SELECT * FROM sushi;
INSERTした結果は下記のようになってしまいます。
mysql> INSERT INTO sushi(name) VALUES("タマゴスシ");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO sushi(name) VALUES("マグロ🍣スシ");
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO sushi(name) VALUES("マグロ🐟スシ");
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT * FROM sushi;
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | タマゴスシ |
| 2 | マグロ |
| 3 | マグロ |
+----+-----------------+
3 rows in set (0.00 sec)
このように、絵文字以降の文字列が途切れた状態で挿入されてしまいます。
（なお別のMySQL5.6環境下だと「Incorrect string value」エラーとなってしまい、INSERTできませんでした）
エラーで終わってくれるなら良いのですが、warningが出ているとはいえ 正常に挿入できていないのにQuery OKで正常終了してしまう のは困ります。
文字コードにutf8mb4を使う
MySQL5.5以上のutf8mb4という文字コードでは、絵文字(4バイト文字)を取り扱うことが出来ます。
/etc/my.cnf 等で、文字コードの設定をutf8からutf8mb4に変更します。
[mysqld]
#character-set-server = utf8
character-set-server = utf8mb4
[client]
#default-character-set = utf8
default-character-set = utf8mb4
設定が反映されているか確認します。
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
この設定のもとで先ほどと同じクエリを実行すると、絵文字が格納できていることがわかります。
mysql> INSERT INTO sushi(name) VALUES("タマゴスシ");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO sushi(name) VALUES("マグロ🍣スシ");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO sushi(name) VALUES("マグロ🐟スシ");
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM sushi;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | タマゴスシ |
| 2 | マグロ🍣スシ |
| 3 | マグロ🐟スシ |
+----+---------------------+
無事に絵文字を挿入することができました。
なおutf8mb4に対応したのはMySQL5.5以上のようです。
実際に、別のMySQL5.1環境下で下記クエリを打ったところ「Unknown character set: 'utf8mb4'」というエラーで失敗してしまいました。
mysql> ALTER TABLE sushiya.sushi CONVERT TO CHARACTER SET utf8mb4;
ERROR 1115 (42000): Unknown character set: 'utf8mb4'
絵文字を検索する
めでたく絵文字を挿入できたのですが、これだけでは検索した時に予期せぬ結果が返ってきてしまいます。
mysql> SELECT * FROM sushi WHERE name = "マグロ🍣スシ";
+----+---------------------+
| id | name |
+----+---------------------+
| 2 | マグロ🍣スシ |
| 3 | マグロ🐟スシ |
+----+---------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM sushi WHERE name = "マグロ🐟スシ";
+----+---------------------+
| id | name |
+----+---------------------+
| 2 | マグロ🍣スシ |
| 3 | マグロ🐟スシ |
+----+---------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM sushi WHERE name = "マグロ🍺スシ";
+----+---------------------+
| id | name |
+----+---------------------+
| 2 | マグロ🍣スシ |
| 3 | マグロ🐟スシ |
+----+---------------------+
2 rows in set (0.00 sec)
絵文字部分がすべて同一文字列だと認識されてしまっているようです。
エラーか何かで終わってくれるなら良いのですが、 検索条件に一致していないデータを返して正常終了してしまう のは、やはり困ります。
照合順序にutf8mb4_binを使う
MySQL と寿司ビール問題 - かみぽわーる によると、照合順序utf8mb4_bin を用いることで解決できるようです。
mysql> CREATE DATABASE sushiya;
Query OK, 1 row affected (0.00 sec)
mysql> USE sushiya;
Database changed
mysql> CREATE TABLE sushi(
-> id INT AUTO_INCREMENT,
-> name VARCHAR(20),
-> INDEX(id)
-> ) COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO sushi(name) VALUES("タマゴスシ");
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO sushi(name) VALUES("マグロ🍣スシ");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO sushi(name) VALUES("マグロ🐟スシ");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM sushi;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | タマゴスシ |
| 2 | マグロ🍣スシ |
| 3 | マグロ🐟スシ |
+----+---------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM sushi WHERE name = "マグロ🍣スシ";
+----+---------------------+
| id | name |
+----+---------------------+
| 2 | マグロ🍣スシ |
+----+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM sushi WHERE name = "マグロ🐟スシ";
+----+---------------------+
| id | name |
+----+---------------------+
| 3 | マグロ🐟スシ |
+----+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM sushi WHERE name = "マグロ🍺スシ";
Empty set (0.00 sec)
これにて、めでたく絵文字を検索もできるようになりました。