41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

３つの「れいわ」がMySQLにちゃんと入るか確認しといた

MySQL

https://qiita.com/tfukumori/items/959ed9c751734b73675fで上がってた話をMySQLで一応調査してみた。

やったこと

https://qiita.com/tfukumori/items/959ed9c751734b73675f であがってた文字コード「U+4EE4」と「U+F9A8」、あと「U+32FF」はいずれもMySQLに入れても大丈夫だよね？文字化けしないよね？（U+32FFは今は保留）という検証。

カラムの型

  • char
  • varchar
  • text

の３つで試した。

また、全文検索が使えるかも確認したかったのでbigramのFULLTEXT INDEXを作成。

検証環境

現場の開発環境で検証。

以下の二つのバージョンで行った。

  • MySQL5.5.20
  • MySQL5.7.17

検証用クエリ

/*
U+4EE4　令
U+F9A8　令
U+32FF　㋿
*/
drop database if exists test;
create database test;

use test;
drop table if exists aaa_utf8mb4;

CREATE TABLE `aaa_utf8mb4` (
  `id` int(11) AUTO_INCREMENT NOT NULL,
  `a` varchar(10) NOT NULL,
  `b` char(10) NOT NULL,
  `c` text     NOT NULL,
  `d` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `i_aaa_utf8mb4_1` (`a`),
  KEY `i_aaa_utf8mb4_2` (`b`),
  KEY `i_aaa_utf8mb4_3` (`c`(10)),
  FULLTEXT KEY `fi_aaa_utf8mb4_4` (`d`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into aaa_utf8mb4(a,b,c,d) values ('令和(U+4EE4)','令和(U+4EE4)','令和(U+4EE4)','明治-大正-昭和-平成-令和(U+4EE4)');
insert into aaa_utf8mb4(a,b,c,d) values ('令和(U+F9A8)','令和(U+F9A8)','令和(U+F9A8)','明治-大正-昭和-平成-令和(U+F9A8)');
insert into aaa_utf8mb4(a,b,c,d) values ('㋿(U+32FF)','㋿(U+32FF)','㋿(U+32FF)','㍾-㍽-㍼-㍻-㋿(U+32FF)');

select * from aaa_utf8mb4;

explain select * from aaa_utf8mb4 where a = '令和(U+4EE4)';
select * from aaa_utf8mb4 where a = '令和(U+4EE4)';
select * from aaa_utf8mb4 where a like '令和%';
explain select * from aaa_utf8mb4 where b = '令和(U+4EE4)';
select * from aaa_utf8mb4 where b = '令和(U+4EE4)';
select * from aaa_utf8mb4 where b like '令和%';
explain select * from aaa_utf8mb4 where c = '令和(U+4EE4)';
select * from aaa_utf8mb4 where c = '令和(U+4EE4)';
select * from aaa_utf8mb4 where c like '令和%';
explain select * from aaa_utf8mb4 where match( d ) against ('+令和' in boolean mode);
select * from aaa_utf8mb4 where match( d ) against ('+令和' in boolean mode);

explain select * from aaa_utf8mb4 where a = '令和(U+F9A8)';
select * from aaa_utf8mb4 where a = '令和(U+F9A8)';
select * from aaa_utf8mb4 where a like '令和%';
explain select * from aaa_utf8mb4 where b = '令和(U+F9A8)';
select * from aaa_utf8mb4 where b = '令和(U+F9A8)';
select * from aaa_utf8mb4 where b like '令和%';
explain select * from aaa_utf8mb4 where c = '令和(U+F9A8)';
select * from aaa_utf8mb4 where c = '令和(U+F9A8)';
select * from aaa_utf8mb4 where c like '令和%';
explain select * from aaa_utf8mb4 where match( d ) against ('+令和' in boolean mode);
select * from aaa_utf8mb4 where match( d ) against ('+令和' in boolean mode);

explain select * from aaa_utf8mb4 where a = '㋿(U+32FF)';
select * from aaa_utf8mb4 where a = '㋿(U+32FF)';
select * from aaa_utf8mb4 where a like '㋿%';
explain select * from aaa_utf8mb4 where b = '㋿(U+32FF)';
select * from aaa_utf8mb4 where b = '㋿(U+32FF)';
select * from aaa_utf8mb4 where b like '㋿%';
explain select * from aaa_utf8mb4 where c = '㋿(U+32FF)';
select * from aaa_utf8mb4 where c = '㋿(U+32FF)';
select * from aaa_utf8mb4 where c like '㋿%';
explain select * from aaa_utf8mb4 where match( d ) against ('+㋿' in boolean mode);
select * from aaa_utf8mb4 where match( d ) against ('+㋿' in boolean mode);


drop table if exists aaa_utf8;

CREATE TABLE `aaa_utf8` (
  `id` int(11) AUTO_INCREMENT NOT NULL,
  `a` varchar(10) NOT NULL,
  `b` char(10) NOT NULL,
  `c` text     NOT NULL,
  `d` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `i_aaa_utf8_1` (`a`),
  KEY `i_aaa_utf8_2` (`b`),
  KEY `i_aaa_utf8_3` (`c`(10)),
  FULLTEXT KEY `fi_aaa_utf8_4` (`d`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into aaa_utf8(a,b,c,d) values ('令和(U+4EE4)','令和(U+4EE4)','令和(U+4EE4)','明治-大正-昭和-平成-令和(U+4EE4)');
insert into aaa_utf8(a,b,c,d) values ('令和(U+F9A8)','令和(U+F9A8)','令和(U+F9A8)','明治-大正-昭和-平成-令和(U+F9A8)');
insert into aaa_utf8(a,b,c,d) values ('㋿(U+32FF)','㋿(U+32FF)','㋿(U+32FF)','㍾-㍽-㍼-㍻-㋿(U+32FF)');

select * from aaa_utf8;

explain select * from aaa_utf8 where a = '令和(U+4EE4)';
select * from aaa_utf8 where a = '令和(U+4EE4)';
select * from aaa_utf8 where a like '令和%';
explain select * from aaa_utf8 where b = '令和(U+4EE4)';
select * from aaa_utf8 where b = '令和(U+4EE4)';
select * from aaa_utf8 where b like '令和%';
explain select * from aaa_utf8 where c = '令和(U+4EE4)';
select * from aaa_utf8 where c = '令和(U+4EE4)';
select * from aaa_utf8 where c like '令和%';
explain select * from aaa_utf8 where match( d ) against ('+令和' in boolean mode);
select * from aaa_utf8 where match( d ) against ('+令和' in boolean mode);

explain select * from aaa_utf8 where a = '令和(U+F9A8)';
select * from aaa_utf8 where a = '令和(U+F9A8)';
select * from aaa_utf8 where a like '令和%';
explain select * from aaa_utf8 where b = '令和(U+F9A8)';
select * from aaa_utf8 where b = '令和(U+F9A8)';
select * from aaa_utf8 where b like '令和%';
explain select * from aaa_utf8 where c = '令和(U+F9A8)';
select * from aaa_utf8 where c = '令和(U+F9A8)';
select * from aaa_utf8 where c like '令和%';
explain select * from aaa_utf8 where match( d ) against ('+令和' in boolean mode);
select * from aaa_utf8 where match( d ) against ('+令和' in boolean mode);

explain select * from aaa_utf8 where a = '㋿(U+32FF)';
select * from aaa_utf8 where a = '㋿(U+32FF)';
select * from aaa_utf8 where a like '㋿%';
explain select * from aaa_utf8 where b = '㋿(U+32FF)';
select * from aaa_utf8 where b = '㋿(U+32FF)';
select * from aaa_utf8 where b like '㋿%';
explain select * from aaa_utf8 where c = '㋿(U+32FF)';
select * from aaa_utf8 where c = '㋿(U+32FF)';
select * from aaa_utf8 where c like '㋿%';
explain select * from aaa_utf8 where match( d ) against ('+㋿' in boolean mode);
select * from aaa_utf8 where match( d ) against ('+㋿' in boolean mode);
  • 元のクエリからそのまま貼ると、レイがU+4EE4っぽく見えていますが、実際は文字コードは変わっているので大丈夫です。
  • U+32FFは今は豆腐だけど、令和になるはず。

実行コマンド

一応クライアント接続時の文字コードも気にしておく(大丈夫だと思うけど)

# MySQL5.7で作業
$ mysql -uhoge -p -hhogehoge57 -Pxxxx --default-character-set=utf8 -t -v <reiwatest.sql  > reiwatest_utf8.log
$ mysql -uhoge -p -hhogehoge57 -Pxxxx --default-character-set=utf8mb4 -t -v <reiwatest.sql  > reiwatest_utf8mb4.log
# MySQL5.5で作業
$ mysql -uhoge -p -hhogehoge55 -PXXXX --default-character-set=utf8 -t -v <reiwatest_55.sql  > reiwatest_utf8_55.log
$ mysql -uhoge -p -hhogehoge55 -PXXXX --default-character-set=utf8mb4 -t -v <reiwatest_55.sql  > reiwatest_utf8mb4_55.log

実行結果

まず全件

--------------
select * from aaa_utf8mb4
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  1 | 令和(U+4EE4)   | 令和(U+4EE4)   | 令和(U+4EE4)   | 明治-大正-昭和-平成-令和(U+4EE4)           |
|  2 | 令和(U+F9A8)   | 令和(U+F9A8)   | 令和(U+F9A8)   | 明治-大正-昭和-平成-令和(U+F9A8)           |
|  3 | ㋿(U+32FF)      | ㋿(U+32FF)      | ㋿(U+32FF)      | ㍾-㍽-㍼-㍻-㋿(U+32FF)                      |
+----+----------------+----------------+----------------+--------------------------------------------+

こんな感じで入ってる。

まず、U+4EE4側の「令」和で検索してみる。

U+4EE4のレイ

--------------
explain select * from aaa_utf8mb4 where a = '令和(U+4EE4)'
--------------

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | ref  | i_aaa_utf8mb4_1 | i_aaa_utf8mb4_1 | 42      | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
--------------
select * from aaa_utf8mb4 where a = '令和(U+4EE4)'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  1 | 令和(U+4EE4)   | 令和(U+4EE4)   | 令和(U+4EE4)   | 明治-大正-昭和-平成-令和(U+4EE4)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
select * from aaa_utf8mb4 where a like '令和%'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  1 | 令和(U+4EE4)   | 令和(U+4EE4)   | 令和(U+4EE4)   | 明治-大正-昭和-平成-令和(U+4EE4)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
explain select * from aaa_utf8mb4 where b = '令和(U+4EE4)'
--------------

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | ref  | i_aaa_utf8mb4_2 | i_aaa_utf8mb4_2 | 40      | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
--------------
select * from aaa_utf8mb4 where b = '令和(U+4EE4)'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  1 | 令和(U+4EE4)   | 令和(U+4EE4)   | 令和(U+4EE4)   | 明治-大正-昭和-平成-令和(U+4EE4)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
select * from aaa_utf8mb4 where b like '令和%'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  1 | 令和(U+4EE4)   | 令和(U+4EE4)   | 令和(U+4EE4)   | 明治-大正-昭和-平成-令和(U+4EE4)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
explain select * from aaa_utf8mb4 where c = '令和(U+4EE4)'
--------------

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | ref  | i_aaa_utf8mb4_3 | i_aaa_utf8mb4_3 | 42      | const |    1 |   100.00 | Using where |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
--------------
select * from aaa_utf8mb4 where c = '令和(U+4EE4)'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  1 | 令和(U+4EE4)   | 令和(U+4EE4)   | 令和(U+4EE4)   | 明治-大正-昭和-平成-令和(U+4EE4)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
select * from aaa_utf8mb4 where c like '令和%'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  1 | 令和(U+4EE4)   | 令和(U+4EE4)   | 令和(U+4EE4)   | 明治-大正-昭和-平成-令和(U+4EE4)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
explain select * from aaa_utf8mb4 where match( d ) against ('+令和' in boolean mode)
--------------

+----+-------------+-------------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table       | partitions | type     | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+-------------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | fulltext | fi_aaa_utf8mb4_4 | fi_aaa_utf8mb4_4 | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking |
+----+-------------+-------------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
--------------
select * from aaa_utf8mb4 where match( d ) against ('+令和' in boolean mode)
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  1 | 令和(U+4EE4)   | 令和(U+4EE4)   | 令和(U+4EE4)   | 明治-大正-昭和-平成-令和(U+4EE4)           |
+----+----------------+----------------+----------------+--------------------------------------------+

インデックスも利いてるし、WHERE句でちゃんと使えてるし、何より全文検索でも表示結果に問題ない。

U+F9A8のレイ

--------------
explain select * from aaa_utf8mb4 where a = '令和(U+F9A8)'
--------------

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | ref  | i_aaa_utf8mb4_1 | i_aaa_utf8mb4_1 | 42      | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
--------------
select * from aaa_utf8mb4 where a = '令和(U+F9A8)'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  2 | 令和(U+F9A8)   | 令和(U+F9A8)   | 令和(U+F9A8)   | 明治-大正-昭和-平成-令和(U+F9A8)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
select * from aaa_utf8mb4 where a like '令和%'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  2 | 令和(U+F9A8)   | 令和(U+F9A8)   | 令和(U+F9A8)   | 明治-大正-昭和-平成-令和(U+F9A8)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
explain select * from aaa_utf8mb4 where b = '令和(U+F9A8)'
--------------

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | ref  | i_aaa_utf8mb4_2 | i_aaa_utf8mb4_2 | 40      | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
--------------
select * from aaa_utf8mb4 where b = '令和(U+F9A8)'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  2 | 令和(U+F9A8)   | 令和(U+F9A8)   | 令和(U+F9A8)   | 明治-大正-昭和-平成-令和(U+F9A8)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
select * from aaa_utf8mb4 where b like '令和%'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  2 | 令和(U+F9A8)   | 令和(U+F9A8)   | 令和(U+F9A8)   | 明治-大正-昭和-平成-令和(U+F9A8)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
explain select * from aaa_utf8mb4 where c = '令和(U+F9A8)'
--------------

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | ref  | i_aaa_utf8mb4_3 | i_aaa_utf8mb4_3 | 42      | const |    1 |   100.00 | Using where |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
--------------
select * from aaa_utf8mb4 where c = '令和(U+F9A8)'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  2 | 令和(U+F9A8)   | 令和(U+F9A8)   | 令和(U+F9A8)   | 明治-大正-昭和-平成-令和(U+F9A8)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
select * from aaa_utf8mb4 where c like '令和%'
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  2 | 令和(U+F9A8)   | 令和(U+F9A8)   | 令和(U+F9A8)   | 明治-大正-昭和-平成-令和(U+F9A8)           |
+----+----------------+----------------+----------------+--------------------------------------------+
--------------
explain select * from aaa_utf8mb4 where match( d ) against ('+令和' in boolean mode)
--------------

+----+-------------+-------------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table       | partitions | type     | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+-------------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | fulltext | fi_aaa_utf8mb4_4 | fi_aaa_utf8mb4_4 | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking |
+----+-------------+-------------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
--------------
select * from aaa_utf8mb4 where match( d ) against ('+令和' in boolean mode)
--------------

+----+----------------+----------------+----------------+--------------------------------------------+
| id | a              | b              | c              | d                                          |
+----+----------------+----------------+----------------+--------------------------------------------+
|  2 | 令和(U+F9A8)   | 令和(U+F9A8)   | 令和(U+F9A8)   | 明治-大正-昭和-平成-令和(U+F9A8)           |
+----+----------------+----------------+----------------+--------------------------------------------+

こっちもオールオッケー。

U+32FFのレイワ

--------------
explain select * from aaa_utf8mb4 where a = '㋿(U+32FF)'
--------------

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | ref  | i_aaa_utf8mb4_1 | i_aaa_utf8mb4_1 | 42      | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
--------------
select * from aaa_utf8mb4 where a = '㋿(U+32FF)'
--------------

+----+-------------+-------------+-------------+-----------------------------+
| id | a           | b           | c           | d                           |
+----+-------------+-------------+-------------+-----------------------------+
|  3 | ㋿(U+32FF)   | ㋿(U+32FF)   | ㋿(U+32FF)   | ㍾-㍽-㍼-㍻-㋿(U+32FF)       |
+----+-------------+-------------+-------------+-----------------------------+
--------------
select * from aaa_utf8mb4 where a like '㋿%'
--------------

+----+-------------+-------------+-------------+-----------------------------+
| id | a           | b           | c           | d                           |
+----+-------------+-------------+-------------+-----------------------------+
|  3 | ㋿(U+32FF)   | ㋿(U+32FF)   | ㋿(U+32FF)   | ㍾-㍽-㍼-㍻-㋿(U+32FF)       |
+----+-------------+-------------+-------------+-----------------------------+
--------------
explain select * from aaa_utf8mb4 where b = '㋿(U+32FF)'
--------------

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | ref  | i_aaa_utf8mb4_2 | i_aaa_utf8mb4_2 | 40      | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
--------------
select * from aaa_utf8mb4 where b = '㋿(U+32FF)'
--------------

+----+-------------+-------------+-------------+-----------------------------+
| id | a           | b           | c           | d                           |
+----+-------------+-------------+-------------+-----------------------------+
|  3 | ㋿(U+32FF)   | ㋿(U+32FF)   | ㋿(U+32FF)   | ㍾-㍽-㍼-㍻-㋿(U+32FF)       |
+----+-------------+-------------+-------------+-----------------------------+
--------------
select * from aaa_utf8mb4 where b like '㋿%'
--------------

+----+-------------+-------------+-------------+-----------------------------+
| id | a           | b           | c           | d                           |
+----+-------------+-------------+-------------+-----------------------------+
|  3 | ㋿(U+32FF)   | ㋿(U+32FF)   | ㋿(U+32FF)   | ㍾-㍽-㍼-㍻-㋿(U+32FF)       |
+----+-------------+-------------+-------------+-----------------------------+
--------------
explain select * from aaa_utf8mb4 where c = '㋿(U+32FF)'
--------------

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | ref  | i_aaa_utf8mb4_3 | i_aaa_utf8mb4_3 | 42      | const |    1 |   100.00 | Using where |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
--------------
select * from aaa_utf8mb4 where c = '㋿(U+32FF)'
--------------

+----+-------------+-------------+-------------+-----------------------------+
| id | a           | b           | c           | d                           |
+----+-------------+-------------+-------------+-----------------------------+
|  3 | ㋿(U+32FF)   | ㋿(U+32FF)   | ㋿(U+32FF)   | ㍾-㍽-㍼-㍻-㋿(U+32FF)       |
+----+-------------+-------------+-------------+-----------------------------+
--------------
select * from aaa_utf8mb4 where c like '㋿%'
--------------

+----+-------------+-------------+-------------+-----------------------------+
| id | a           | b           | c           | d                           |
+----+-------------+-------------+-------------+-----------------------------+
|  3 | ㋿(U+32FF)   | ㋿(U+32FF)   | ㋿(U+32FF)   | ㍾-㍽-㍼-㍻-㋿(U+32FF)       |
+----+-------------+-------------+-------------+-----------------------------+
--------------
explain select * from aaa_utf8mb4 where match( d ) against ('+㋿' in boolean mode)
--------------

+----+-------------+-------------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table       | partitions | type     | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+-------------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | aaa_utf8mb4 | NULL       | fulltext | fi_aaa_utf8mb4_4 | fi_aaa_utf8mb4_4 | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking |
+----+-------------+-------------+------------+----------+------------------+------------------+---------+-------+------+----------+-----------------------------------+
--------------
select * from aaa_utf8mb4 where match( d ) against ('+㋿' in boolean mode)
--------------

全文検索では合字使えないね。

とはいえちゃんと登録されているようだし、検索時もidを見る限り正しいそれを取得できている様子。(今は豆腐だけどさ・・・)

ちなみにutf8テーブルのほうも結果は上記と全く同じだし、--default-character-setがutf8とutf8mb4で変わることはなかった。 (あったらまずいんだけどね)

追記

collationのこと忘れてた。 MySQL5.5系はデフォがutf8_general_ci、MySQL5.7系はutf8mb4_general_ciなのでそれらで作られたと思ってください。