15
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

MySQLAdvent Calendar 2021

Day 22

【MySQL】空文字だけを抽出するつもりが半角スペースも抽出されたんだけどどういうこと?

Last updated at Posted at 2021-12-21

この記事は 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_ciPAD 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

参考

15
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
15
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?