MySQL 8.0 でも utf8mb4_general_ci を使い続けたい僕らは
このエントリーは MySQL Advent Calendar 2020 の 12/7 のエントリーです。
照合順序（COLLATION）とは
照合順序は文字列の比較やソート順のルールのことです。各キャラクタセットごとに照合順序が定義されています。
-- SHOW COLLATIONS で一覧が見れる mysql> SHOW COLLATIONS; +----------------------------+----------+-----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +----------------------------+----------+-----+---------+----------+---------+---------------+ | armscii8_bin | armscii8 | 64 | | Yes | 1 | PAD SPACE | | armscii8_general_ci | armscii8 | 32 | Yes | Yes | 1 | PAD SPACE | | ascii_bin | ascii | 65 | | Yes | 1 | PAD SPACE | | ascii_general_ci | ascii | 11 | Yes | Yes | 1 | PAD SPACE | | big5_bin | big5 | 84 | | Yes | 1 | PAD SPACE | | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | PAD SPACE | | binary | binary | 63 | Yes | Yes | 1 | NO PAD | <snip>
MySQL 8.0 で、utf8mb4 の照合順序が増えました。以下の表で太字にしたものが、新規に追加されたものです。各文字列が、同一と扱われる場合は、○としています。
ci/cs は Case [In]sensitive の略で、as は Acent Sensiteve、ks は Katakana Sensitive の略です。
| COLLATION | A、a | はは、ぱぱ | はは、ハハ | びょういん、びよういん | 🍣、🍺 | ＋、+ |
|---|---|---|---|---|---|---|
| utf8mb4_bin | × | × | × | × | × | × |
| utf8mb4_0900_bin | × | × | × | × | × | × |
| utf8mb4_unicode_ci | ○ | ○ | ○ | ○ | ○ | ○ |
| utf8mb4_general_ci | ○ | × | × | × | ○ | × |
| utf8mb4_unicode_520_ci | ○ | ○ | ○ | ○ | × | ○ |
| utf8mb4_0900_ai_ci | ○ | ○ | ○ | ○ | × | ○ |
| utf8mb4_0900_as_ci | ○ | × | ○ | ○ | × | ○ |
| utf8mb4_ja_0900_as_cs | × | × | ○ | × | × | ○ |
| utf8mb4_ja_0900_as_cs_ks | × | × | × | × | × | ○ |
アルファベットの大文字・小文字を区別しない要件で、どれが選ばれそうか・・・
utf8mb4_0900_as_ci は「びょういん」「びよういん」が同一と扱われてしまい、いまいちに感じます。
そもそも、日本語の文字列比較やソート結果を網羅的に精査するのは現実的に可能なんでしょうか（上記の表以外にも考えないといけない、パターンがありそうです）。日本語には異字体・長音記号・漢数字・・・ちょっと思いつくだけでも、扱いに悩みそうな要素が多くあります。
絵文字が区別できないとは言え、utf8mb4_general_ci にはずっと使ってきた実績と安心があります。
MySQL 8.0 でも utf8mb4_general_ci を 引き続き使うケースが多いのではないでしょうか。
MySQL 8.0 で utf8mb4_general_ci を使うときの注意点
ALTER TABLE CONVERT TO 時に COLLATION の指定が必要
MySQL 8.0 で utf8mb4 のデフォルトの照合順序が utf8mb4_general_ci から utf8mb4_0900_as_ci に変更になりました。
あわせて、従来の3バイトUTF8、utf8(mb3) は deprecated になっています。
utf8mb4 に変換するときに COLLATE を明示的に指定しないと、utf8_general_ci から utf8mb4_0900_ai_ci へとテーブルのデフォルト照合順序になってしまいます。
mysql> SELECT * FROM utf8t WHERE c1 = "ぱぱ";
Empty set (0.00 sec)
mysql> ALTER TABLE utf8t CONVERT TO CHARACTER SET 'utf8mb4';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 「ぱぱ」で「はは」がヒットしてしまう
mysql> SELECT * FROM utf8t WHERE c1 = "ぱぱ";
+----+--------+
| pk | c1 |
+----+--------+
| 1 | はは |
+----+--------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE utf8t \G
*************************** 1. row ***************************
Table: utf8t
Create Table: CREATE TABLE `utf8t` (
`pk` bigint unsigned NOT NULL AUTO_INCREMENT,
`c1` varchar(255) DEFAULT NULL,
UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
このように、COLLATE を指定してALTERする必要があります。
mysql> ALTER TABLE utf8t CONVERT TO CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE utf8t \G
*************************** 1. row ***************************
Table: utf8t
Create Table: CREATE TABLE `utf8t` (
`pk` bigint unsigned NOT NULL AUTO_INCREMENT,
`c1` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
SET NAMES では COLLATE の指定が必要
同様に、SET NAMES で照合順序を明示的に指定していない場合、MySQL 8.0 からは utf8mb4_0900_as_ci が使われてしまいます。
# MySQL 8.0 以降は utf8mb4_0900_as_ci が使われる mysql> SET NAMES utf8mb4; # MySQL 8.0 以降は 明示的に utf8mb4_general_ci を指定する必要がある。 mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
過去のMySQL Advent Calendar
気付けば、MySQL Advent Calendar を 6年も続けてました・・・。時が経つのは早い・・・。 明日は、lhfukamachi さんです！
- 2015 qiita.com
- 2016 qiita.com
- 2017 mita2db.hateblo.jp
- 2018 mita2db.hateblo.jp
- 2019 mita2db.hateblo.jp
