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

この記事は [MySQL Advent Calendar 2021](https://qiita.com/advent-calendar/2021/mysql) 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の照合順序の名称には規則があるため、詳しく知りたい場合は[こちら](https://dev.mysql.com/doc/refman/8.0/ja/charset-collation-names.html)を参照するとよい。

#### パッド属性とは
先程、実際に`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

- 比較での後続領域の処理
https://dev.mysql.com/doc/refman/8.0/ja/charset-binary-collations.html#charset-binary-collations-trailing-space-comparisons

- 照合順序(COLLATE)について
https://sowel.co.jp/PDF_file/SQL/Other/AboutCollate.pdf

- MySQLの照合順序を修正する
https://qiita.com/devzooiiooz/items/d8e774d0c8be718e89a1