MySQL道普請便り

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

皆さんはカラムに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を組み合わせて使用する場合には、非常に厄介なものになると思います。用法用量をよく守って便利に使用していきましょう。

おすすめ記事

記事・ニュース一覧