At. 慶 Technology

MySQL8 でベースにする文字コードはutf8mb4_unicode_ci

照合順序（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;