この記事は、LayerX Tech Advent Calendar 2024 の16日目の記事です。
はじめに
こんにちは。
バクラク事業部エンジニアの石黒です。
今回は、MySQLを利用した開発をしていて、大文字・小文字が同じ文字として判定されたり、末尾空白が無視されて困った話を共有します。
ある日、いいかんじに開発していると、MySQLで不思議なことが起こりました。
以下のような users テーブルがあったとき、
| id (PK) | name (Unique Key) | created_at | updated_at |
|---|---|---|---|
| 2C420DA8-62E9-43A6-8F52-62EB5D210F42 | alice | 2024-12-10T21:39:44+0900 | 2024-12-10T21:39:44+0900 |
| 179DE0D4-B923-43FB-BB59-DD77A611C7A2 | bob | 2024-12-10T21:39:44+0900 | 2024-12-10T21:39:44+0900 |
DDL
create table users ( id varchar(36) primary key, name varchar(50) collate utf8mb4_general_ci not null unique key, created_at timestamp default current_timestamp not null, updated_at timestamp default current_timestamp not null on update current_timestamp );
新しく ‘Alice’ という名前のユーザーを追加しようとすると、
mysql> insert into users values ('72EDF2DE-C1B4-44EF-AB47-1650750D9CDF', 'Alice', now(), now()); Duplicate entry 'Alice' for key 'users. name'
name の unique key 制約に引っかかっています。‘alice’はあるけど、’Alice’はまだないはずなのに。
なんならselectで後ろにどれだけ空白入れてもヒットしちゃう。
mysql> select * from users where name = 'alice '; +--------------------------------------+-------+---------------------+---------------------+ | id | name | created_at | updated_at | +--------------------------------------+-------+---------------------+---------------------+ | 2C420DA8-62E9-43A6-8F52-62EB5D210F42 | alice | 2024-12-16 00:00:00 | 2024-12-16 00:00:00 | +--------------------------------------+-------+---------------------+---------------------+
私のMySQL、壊れちゃった？？
TL;DR
MySQL の collation が utf8mb4_general_ci に設定されているためです
- この Collation は Case Insensitive なため、大文字・小文字を同じ文字として判定します
- また、pad_attribute が
PAD SPACEであるため、末尾の空白も無視されて判定されます
mysql> select collation_name, pad_attribute
from information_schema.collations
where collation_name = 'utf8mb4_general_ci';
+--------------------+---------------+
| collation_name | pad_attribute |
+--------------------+---------------+
| utf8mb4_general_ci | PAD SPACE |
+--------------------+---------------+
MySQL の照合順序について
冒頭で例示した、大文字・小文字が同じ文字として判定されたり、末尾の空白が無視されたりする挙動はMySQLの collation (コレーション) の設定によるものです。
TL;DRに書いたことがすべてではありますが、今回は自分が collation についてよく知らなかったため、collationとその周辺知識についてまとめました。
私と同様に、ふんわりとしか知らなかったという方はぜひご一読ください。
collation とは
collation は、文字を「どのように文字を比べて、並べるか」を定義した一連のルールです。
たとえば、大文字と小文字を区別するか否か、アクセント付き文字（é や è）を素の文字（e）と同字扱いにするかどうかといった違いがあります。
collation は、 character set と同時に設定され、1つの character set に対して複数の collation を持ちます。
例えば、以下は character set utf8mb4 のcollation一覧です。
utf8mb4 の collation 一覧
mysql> select collation_name, character_set_name, pad_attribute
from information_schema.collations
where character_set_name = 'utf8mb4';
+----------------------------+--------------------+---------------+
| collation_name | character_set_name | pad_attribute |
+----------------------------+--------------------+---------------+
| utf8mb4_general_ci | utf8mb4 | PAD SPACE |
| utf8mb4_bin | utf8mb4 | PAD SPACE |
| utf8mb4_unicode_ci | utf8mb4 | PAD SPACE |
| utf8mb4_icelandic_ci | utf8mb4 | PAD SPACE |
| utf8mb4_latvian_ci | utf8mb4 | PAD SPACE |
| utf8mb4_romanian_ci | utf8mb4 | PAD SPACE |
| utf8mb4_slovenian_ci | utf8mb4 | PAD SPACE |
| utf8mb4_polish_ci | utf8mb4 | PAD SPACE |
| utf8mb4_estonian_ci | utf8mb4 | PAD SPACE |
| utf8mb4_spanish_ci | utf8mb4 | PAD SPACE |
| utf8mb4_swedish_ci | utf8mb4 | PAD SPACE |
| utf8mb4_turkish_ci | utf8mb4 | PAD SPACE |
| utf8mb4_czech_ci | utf8mb4 | PAD SPACE |
| utf8mb4_danish_ci | utf8mb4 | PAD SPACE |
| utf8mb4_lithuanian_ci | utf8mb4 | PAD SPACE |
| utf8mb4_slovak_ci | utf8mb4 | PAD SPACE |
| utf8mb4_spanish2_ci | utf8mb4 | PAD SPACE |
| utf8mb4_roman_ci | utf8mb4 | PAD SPACE |
| utf8mb4_persian_ci | utf8mb4 | PAD SPACE |
| utf8mb4_esperanto_ci | utf8mb4 | PAD SPACE |
| utf8mb4_hungarian_ci | utf8mb4 | PAD SPACE |
| utf8mb4_sinhala_ci | utf8mb4 | PAD SPACE |
| utf8mb4_german2_ci | utf8mb4 | PAD SPACE |
| utf8mb4_croatian_ci | utf8mb4 | PAD SPACE |
| utf8mb4_unicode_520_ci | utf8mb4 | PAD SPACE |
| utf8mb4_vietnamese_ci | utf8mb4 | PAD SPACE |
| utf8mb4_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_de_pb_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_is_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_lv_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_ro_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_sl_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_pl_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_et_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_es_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_sv_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_tr_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_cs_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_da_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_lt_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_sk_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_la_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_eo_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_hu_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_hr_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_vi_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_de_pb_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_is_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_lv_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_ro_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_sl_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_pl_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_et_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_es_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_sv_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_tr_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_cs_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_da_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_lt_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_sk_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_es_trad_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_la_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_eo_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_hu_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_hr_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_vi_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_ja_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_ja_0900_as_cs_ks | utf8mb4 | NO PAD |
| utf8mb4_0900_as_ci | utf8mb4 | NO PAD |
| utf8mb4_ru_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_ru_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_zh_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_0900_bin | utf8mb4 | NO PAD |
| utf8mb4_nb_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_nb_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_nn_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_nn_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_sr_latn_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_sr_latn_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_bs_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_bs_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_bg_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_bg_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_gl_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_gl_0900_as_cs | utf8mb4 | NO PAD |
| utf8mb4_mn_cyrl_0900_ai_ci | utf8mb4 | NO PAD |
| utf8mb4_mn_cyrl_0900_as_cs | utf8mb4 | NO PAD |
+----------------------------+--------------------+---------------+
めっちゃある
collation の命名規則
collation名は、utf8mb4_0900_bin のように {{character set}}_{{ルールセット}}_{{suffix}} の組み合わせになっています。
これさえ理解できていれば、collation名から照合の挙動を推測することができます。
ルールセット (_general or _0900)
_general 系
従来から存在する汎用的な照合順序で、Unicode 標準の比較規則を簡略化したものがベースになっています。
比較的単純なロジックなため、理論的には高速に動きますが、国際化が進んだ近年の要件には対応しきれない場合があります。
_0900系
MySQL 8.0 以降で導入された、Unicode Collation Algorithm (UCA) バージョン9.0に基づく照合順序です。
アクセント、記号、さまざまな文字スクリプトなどのUnicode標準が規定する細かい比較ルールが反映され、より言語学的・文化的に正確なソート・比較が可能 (たとえば、フランス語の「é」と「e」を明確に区別したり、ドイツ語のウムラウト（「ä」「ö」「ü」）を正しく評価するなど) になっていて、_general系より期待通りの並び替えや比較結果が得られます。
MySQL 8.0 以降であれば、0900系を使うと混乱が少なくて良いかなと思います。 (MySQL8のデフォルトは utf8mb4_0900_ai_ci。)
Suffix
collation 名には、_ci, _cs, _ai, _as, _ks, _binといったサフィックスが付きます。
Suffix は、比較時の大文字・小文字やアクセントなどの比較ルールを表しています。
| Suffix | 特徴 |
|---|---|
| _ci | Case Insensitive（大文字・小文字を区別しない） |
| _cs | Case Sensitive（大文字・小文字を区別する） |
| _ai | Accent Insensitive（アクセントを区別しない） |
| _as | Accent Sensitive（アクセントを区別する） |
| _ks | Kana Sensitive（かなの全角/半角などを区別する） |
| _bin | Binary比較（バイナリレベルで区別） |
MySQL :: MySQL 8.0 リファレンスマニュアル :: 10.3.1 照合の命名規則
PAD_ATTRIBUTE
collation には、PAD_ATTRIBUTEという設定があり、 PAD SPACE or NO PAD のどちらかをとります。
PAD SPACE 設定の collation では、末尾空白は無視されて比較されます。
character set, collationの設定・確認方法
最後に、character set と collation の設定・確認方法を確認します。
collationは、サーバー、データベース、テーブル、カラムの4つのレベルでそれぞれ設定できます。
サーバーレベル
mysql> show variables like 'character_set_server'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | character_set_server | utf8mb4 | +----------------------+---------+ mysql> show variables like 'collation_server' or variables like 'character_set_server'; +------------------+--------------------+ | Variable_name | Value | +------------------+--------------------+ | collation_server | utf8mb4_0900_ai_ci | +------------------+--------------------+
データベースレベル
create database時に、character set, collationをそれぞれ指定できます。
create database `<database_name>` default character set utf8mb4 default collate utf8mb4_0900_ai_ci;
mysql> show create database `<database_name>`; +-----------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +-----------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | <database_name> | CREATE DATABASE `<database_name>` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +-----------------+-------------------------------------------------------------------------------------------------------------------------------------------+
この時、character set や collation の指定は省略できますが、その場合、以下のように設定される点に注意が必要です。
- character set のみ指定: character set に紐づくデフォルトの collation が設定されます
- 何も指定しない: サーバーレベルの character set, collation が設定されます
テーブル・カラムレベル
-- Table level create table table_name ( id varchar(36) primary key, text_col varchar(100) ) character set utf8mb4 collate utf8mb4_0900_ai_ci; -- Column level create table table_name ( id varchar(36) primary key, text_col varchar(100) character set utf8mb4 collate utf8mb4_bin );
指定を省略した場合は、データベースレベルと同様により上位の character set, collation が設定されます。
結局どの collation にすればいいの
局面やユースケースによって使い分けるのが理想ですが、もし日本語での利用を前提として1つ選ぶとするなら、個人的にはMySQL 8以降で利用可能なutf8mb4のcollationの中では utf8mb4_0900_bin が便利だと感じます。 (バイナリ比較 かつ no pad仕様であるため)
個人的には、意図しないデータの取得やINSERTエラーを防ぐためにも、(DBレイヤではなく) アプリケーション側に仕様に基づいたバリデーションを実装した上で、データベース層では厳密な比較が行われるようにするのが良いと考えます。
おわりに
MySQL の collationについて、基本的な概念や設定方法についてまとめました。
普段から使用している MySQL の文字列比較の仕組みについて、より深い理解を得る機会となれば幸いです。
LayerXでは、一緒にはたらくをバクラクにしてくれる仲間を募集しています。ちょっとでも興味のある方は一度ぜひお話しましょう！
