DMS同期テーブルにユニークINDEXを貼ったら「テスト」と「てすと」が同一扱いされた話

はじめに

脱Oracleすべく、OracleからMySQLにAWSのDMS（Database Migration Service）でデータを同期しました。
その後、MySQL側でユニークINDEXを貼ろうとしたら謎のエラーが発生…。
原因を追ったところ、照合順序（collation） が落とし穴でした、という話です。

事象

ユニークINDEX追加時にエラー

ALTER TABLE users ADD UNIQUE INDEX idx_name (name);

ERROR 1062 (23000): Duplicate entry 'テスト' for key 'idx_name'

しかし、テーブルには「テスト」と「てすと」が別々に入っているはず。

SELECT name FROM users where 'テスト'

| name  |
|-------|
| テスト |
| てすと |

!?
「なんでや…」状態。

同様に、大文字・小文字（abc と Abc）も区別されません。

原因

調査したところ、原因は「照合順序」でした。
DMSで同期されたテーブルの照合順序が、MySQL8のデフォルトであるutf8mb4_0900_ai_ciになっていたようです。

そもそも照合順序とは

• 文字列の「比較・ソートの仕方」を決める設定
• 同じ文字かどうか、大文字小文字を区別するか、濁点をどう扱うか…などを決定する

utf8mb4_0900_ai_ciについて詳しく

• ai = accent-insensitive（濁点や半濁点を区別しない）
• ci = case-insensitive（大文字小文字を区別しない）
• つまりひらがなとカタカナ、半角と全角、大文字と小文字が同一視される

日本語に優しくない…

照合順序の確認方法

カラムごとの照合順序はSHOW FULL COLUMNSで確認できる模様。

SHOW FULL COLUMNS FROM users;

結果：

| name | varchar(255) | utf8mb4_0900_ai_ci |

やっぱりそうか…。

対応

照合順序をバイナリソート(utf8mb4_bin)に変更。

ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

クエリの結果も想定通りになりました。

SELECT name FROM users where 'テスト'

| name  |
|-------|
| テスト |

また再度インデックスを貼ると成功しました🎉

学び

• MySQL 8以降のデフォルト照合順序は utf8mb4_0900_ai_ci
→ 日本語ではひらがな/カタカナ、半角/全角が同一視される
• 日本語を扱うならutf8mb4_binやutf8mb4_ja_0900_as_csを明示的に選ぼう
• インデックスを貼る前にSHOW FULL COLUMNSやinformation_schema.COLUMNSでCOLLATEを確認すると安心

まとめ

今まで照合順序をなんとなくでしか意識していなかったので反省しました…。
次からはテーブル作成時にちゃんとCOLLATEを明示するようにします。