mysqlで4bytes 絵文字が含まれているか検索する

tableのcharacter set を utf8mb4 にしておくことで、4bytes 絵文字をテーブルに格納することができます。
実際に運用し始めた後に、そういった絵文字が入っているのか？が気になったときに調べる方法です。

結論

select * from test  where hex(w) regexp '^(..)*(F0)'; 

でチェックできます。

試してみる

• dockerでmysqlを立ち上げる

docker run --rm -e MYSQL_ROOT_PASSWORD=a -e MYSQL_DATABASE=test -p 3306:3306 mysql:5.7  

• コンソールからアクセス

mysql -u root -pa -h 127.0.0.1  test

• 文字コードを変更

set character_set_client="utf8mb4";
set character_set_connection="utf8mb4";
set character_set_database="utf8mb4";
set character_set_results="utf8mb4";
set character_set_server="utf8mb4";

• テストデータを作成

create table test (
  w text
) charset=utf8mb4;


insert into test values
("alpha"),
("日本語"),
("⛩"),
("これは3byte⛩"),
("⛩なので引っかからないのが正しいです"),
("😀"),
("これは4byte😀"),
("😀なので引っかかるのが正しいです"),
;

• テスト！

mysql> select * from test  where hex(w) regexp '^(..)*(F0)';
+---------------------------------------------------+
| w                                                 |
+---------------------------------------------------+
| 😀                                                  |
| これは4byte😀                                       |
| 😀なので引っかかるのが正しいです                    |
+---------------------------------------------------+
3 rows in set (0.00 sec)

無事絵文字が含まれた文字列を検索できました。