MySQL道普請便り

第144回 MySQLの<=>演算子を使ってみる

この記事を読むのに必要な時間:およそ 3 分

皆さんはカラムにNULLが入らないようにするNOT NULL制約を積極的に使っていますか? もし使うにしても,どうしてもカラムにNULLを入れる必要があったり,過去からのしがらみなどから,どうしても付けられていないという場合もあると思います。そんな時に,厄介に感じるのはNULL値の値です。NULL値の比較に通常の=による比較演算子を使用すると,想定した結果が得られないためバグにつながる可能性もあります。

今回は,NULL値が入る可能性がある場合に便利な<=>演算子について紹介していきます。

検証環境

今回は第125回 phpMyAdminでDockerで建てたMySQLにアクセスするで記載したdocker-composeを利用して作成します。手元で簡単に試せるように,GitHubのわたしのレポジトリにサンプルコードとして置いてあるので,気軽に試したい方はgit cloneして試してみてください。試すにはdockerとdocker-composeが必要です。

NULL値の比較に関して

まずはNULL値の比較を始める前に,数値同士の比較をしてみましょう。以下のクエリは数値の1と1の比較なので,真偽値でいうと真となります。ちなみにMySQLの真偽値は1とそれ以外で扱われるので,1が真でNULLも0も同じく偽という扱いです。

mysql> select 1 = 1;
+-------+
| 1 = 1 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

結果は1が帰ってきました。1と1を比較して真になりました。続いて1と0を比較して結果が偽になる場合を試してみます。

mysql> select 1 = 0;
+-------+
| 1 = 0 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

結果は0なので,上記に書いたとおりで,結果が偽になっていることがわかります。では続けて本題のNULL値との比較を試してみましょう。はじめは数値とNULLの比較です。

mysql> select 1 = NULL;
+----------+
| 1 = NULL |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

結果がNULLになりました。

続いて,NULLとNULLの比較をしてみましょう。NULLとNULLでは真になりそうな気がするのですが,どうなるでしょうか?

mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

結果は真になるかと思いきや,上記のようにNULLになります。これは直感に反しているように感じられる挙動になると思います。NULLとNULLで同じものとして扱いたい場合はIS NULLを使用する必要があります。

mysql> SELECT NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

NULL同士の比較をする場合には注意が必要です。

ちょっと脇道にそれてしまうのですが,SELECT NULL = NULL IS NULL;を実行すると結果が何になるかわかるでしょうか? こちらは試してみるとすぐにわかって,答えは1になります。

mysql> select NULL = NULL IS NULL;
+---------------------+
| NULL = NULL IS NULL |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

このように,演算子の順序に迷ってしまうような場合には,公式のドキュメントの演算子の優先順位の項目を12.4.1 Operator Precedenceを見て理解してみると良いと思います。

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

上記はドキュメントから抜粋した演算子の優先順位で,上から順に優先的に実行されます。横並びになっている場合は左から順に実行されるため,今回は=(comparison)ISで同じ行にありますが,=(comparison)のほうが先に評価されて,その後でISが評価されていることがわかります。

<=>演算子を使ってみる

<=>演算子は見た目が非常に宇宙船に似ている事から,Spaceship Operatorや,UFO演算子等と呼ばれることも多いですが,MySQLの公式のドキュメントでは「NULL-safe equal」「NULL安全等価演算子」と呼ばれます。

ここでは,<=>を使って先ほどの比較をもう一度行ってみましょう。

1と1を比較した場合と,1と0を比較した場合は以下のようになります。

mysql> select 1 <=> 1 , 1 <=> 0;
+---------+---------+
| 1 <=> 1 | 1 <=> 0 |
+---------+---------+
|       1 |       0 |
+---------+---------+
1 row in set (0.00 sec)

上記は,通常の比較と変わらないことがわかります。SELECT文で計算するときには,通常のクエリを書くときと同じで,,でつないで一度に出力することができます。では続いて1とNULLの比較を行ってみましょう。

mysql> select 1 <=> NULL;
+------------+
| 1 <=> NULL |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

結果がNULLではなくて,0になっていることがわかります。先の項目でも説明しましたが,0でもNULLでも変わらないので,WHERE句で使用する分にはあまり意味が無いようにも思えますが,計算した結果を利用する場合には結果が変わるので,少しだけ注意が必要です。続いてNULLとNULLの比較を行ってみましょう。

mysql> select NULL <=> NULL;
+---------------+
| NULL <=> NULL |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

結果が通常の=での比較と結果が変わって,1になっていることがわかります。このように,NULL以外の値は=演算子と同様に比較し,NULLが入ってきた場合にはIS NULLと同じような挙動をしてくれる機能になります。

こちらは非常に便利な演算子です。特に最近では自作することはあまりないと思いますが,クエリビルダーなどを作る際に,NULLとそれ以外で挙動を変える必要がなくなるので,シンプルに記載することができるようになります。

IS NOT NULLを表すにはどうしたら良いのか?という話はあると思いますが,結果はただの真偽値なので,NOT演算子と組み合わせて以下のように表せます。

mysql> SELECT NOT( 1 <=> NULL), NOT( NULL <=> NULL);
+------------------+---------------------+
| NOT( 1 <=> NULL) | NOT( NULL <=> NULL) |
+------------------+---------------------+
|                1 |                   0 |
+------------------+---------------------+
1 row in set (0.00 sec)

ただし,これはMySQL固有の記法です。他のRDBMSを利用する場合などには混乱の元となってしまうため,利用しないほうが良いかもしれません。

まとめ

今回はMySQLの固有の機能である<=>演算子について紹介しました。<=>演算子を使うと,NULL値も他の値と同様に比較することができるため,扱いが非常に容易になります。

特にNULLが来るかどうかよくわからない値のプレースホルダーと組み合わせて使用すると,NULL値とそれ以外でISと=での使い分けを考えずに済むため,非常に簡単にクエリを組み立てることができます。

ただ,こんなに便利な<=>演算子ですが,こちらSQL標準のものではないため,MySQL以外のRDBMSへの移行を行う場合や,MySQL以外のRDBMSを組み合わせて使用する場合には,非常に厄介なものになると思います。用法用量をよく守って便利に使用していきましょう。

著者プロフィール

木村浩一郎(きむらこういちろう)

株式会社オプティム 技術統括本部のエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter:@kk2170