この記事は MySQL Advent Calendar 2021 22日目の記事です。
はじめに
MySQLにてある項目が空文字のレコードを抽出しようとした際に半角スペースのレコードも抽出されてしまう現象に遭遇した。
この記事ではその現象について調査してみたことをまとめる。
動作環境
$ mysql --version
mysql Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
現象の確認
以下のようなテーブルにある項目が空文字のレコードと半角スペース1文字のレコードが存在したとする。
mysql> desc sample_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| item | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> select * from sample_table;
+----+------+
| id | item |
+----+------+
| 1 | | # 空文字のレコード
| 2 | | # 半角スペースのレコード
+----+------+
このとき、以下のSQLを発行した結果はこのようになった。
mysql> select * from sample_table where item = '';
+----+------+
| id | item |
+----+------+
| 1 | |
| 2 | |
+----+------+
空文字のレコードは1件しか無いはずが、2件抽出されてしまった。
これは一体どういうことなのだろうか？
なぜこのようなことが起きるのか
この現象は照合順序のパッド属性によって決まってくる。
そのため、照合順序とパッド属性について理解していこう。
照合順序とは
照合順序というのは文字の順序を示したもので、例えば、
- 'A'、'B'という文字の順序は'A'が先である。
- 'a'、'A'という文字の順序は'A'が先である。
といった文字の順序を示すものであり、Collationと呼ばれるものである。
また、照合順序によっては、大文字と小文字を区別しない、全角と半角を区別しないというものが存在したりする。
MySQLではshow collationコマンドを使ってサポートしている照合順序を参照できる。
実際に参照してみると以下のような内容を得ることができる。
（参照時にlikeで照合順序名の絞り込みが可能）
mysql> show collation like 'utf8mb4%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |
| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | PAD SPACE |
| utf8mb4_cs_0900_ai_ci | utf8mb4 | 266 | | Yes | 0 | NO PAD |
・・・
Collation列が照合順序の名称となる。
MySQLの照合順序の名称には規則があるため、詳しく知りたい場合はこちらを参照するとよい。
パッド属性とは
先程、実際にshow collationした結果にPad_attributeという列があるが、これがパッド属性である。
このパッド属性には以下の2つが存在し、それぞれ動作が異なってくる。
PAD SPACE
末尾のスペースは無視して比較されるNO PAD
末尾のスペースまで比較される
つまり、照合順序のパッド属性がPAD SPACEの場合に今回のような現象が起きることになる。
実際に確認してみる
ここからは実際に照合順序のパッド属性を確認しながら、空文字の抽出がどのように変わるか見てみよう。
カラムの照合順序を確認する
照合順序はカラムごとに設定可能であるため、カラムの照合順序を確認してみる。
カラムの照合順序を確認したい場合はshow full columns from テーブル名で確認することができる。
mysql> show full columns from sample_table;
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id | int | NULL | NO | PRI | NULL | | select,insert,update,references | |
| item | varchar(20) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
itemカラムの照合順序（Collation）はutf8mb4_general_ciであることが分かる。
utf8mb4_general_ciのパッド属性は何か確認してみる。
mysql> show collation like 'utf8mb4_general_ci';
+--------------------+---------+----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+--------------------+---------+----+---------+----------+---------+---------------+
| utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE |
+--------------------+---------+----+---------+----------+---------+---------------+
結果からutf8mb4_general_ciはPAD SPACEであることが分かる。
PAD SPACEの場合の挙動の確認
PAD SPACEは末尾のスペースは無視して比較するため、空文字で抽出しても2件抽出される。
mysql> select * from sample_table where item = '';
+----+------+
| id | item |
+----+------+
| 1 | |
| 2 | |
+----+------+
カラムの照合順序を変更する
次にNO PADの場合を確認するため、カラムの照合順序をパッド属性がNO PADのものに変更する。
カラムの照合順序を変更する場合は以下のようにalter tableで照合順序を指定する。
mysql> show collation like 'utf8mb4%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD | # この照合順序を使う
・・・
# 照合順序を変更する
mysql> alter table sample_table modify column item varchar(20) collate 'utf8mb4_0900_ai_ci';
# 変更されているか確認する
mysql> show full columns from sample_table;
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id | int | NULL | NO | PRI | NULL | | select,insert,update,references | |
| item | varchar(20) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
NO PADの場合の挙動の確認
NO PADは末尾のスペースまで比較するため、空文字で抽出した場合は1件抽出される。
mysql> select * from sample_table where item = '';
+----+------+
| id | item |
+----+------+
| 1 | |
+----+------+
まとめ
空文字を抽出しようとした場合、照合順序のパッド属性によって結果が変わってくる。
PAD SPACE：末尾のスペースは無視して比較される
NO PAD：末尾のスペースまで比較される
今回は照合順序を変更しながら空文字のレコードを抽出した際の挙動を確認してみた。
そもそも照合順序という言葉すら知らなかったが、この現象をきっかけに調べてみて理解を深められたと思う。
同じような疑問を持ったことがある人の助けになれば幸いだ。
それではまた。
TomoProg
参考
10.3.1 照合の命名規則
https://dev.mysql.com/doc/refman/8.0/ja/charset-collation-names.html照合順序(COLLATE)について
https://sowel.co.jp/PDF_file/SQL/Other/AboutCollate.pdfMySQLの照合順序を修正する
https://qiita.com/devzooiiooz/items/d8e774d0c8be718e89a1