皆さんはカラムに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も同じく偽という扱いです。
結果は1が帰ってきました。1と1を比較して真になりました。続いて1と0を比較して結果が偽になる場合を試してみます。
結果は0なので、上記に書いたとおりで、結果が偽になっていることがわかります。では続けて本題のNULL値との比較を試してみましょう。はじめは数値とNULLの比較です。
結果がNULLになりました。
続いて、NULLとNULLの比較をしてみましょう。NULLとNULLでは真になりそうな気がするのですが、どうなるでしょうか?
結果は真になるかと思いきや、上記のようにNULLになります。これは直感に反しているように感じられる挙動になると思います。NULLとNULLで同じものとして扱いたい場合はIS NULL
を使用する必要があります。
NULL同士の比較をする場合には注意が必要です。
ちょっと脇道にそれてしまうのですが、SELECT NULL = NULL IS NULL;
を実行すると結果が何になるかわかるでしょうか? こちらは試してみるとすぐにわかって、答えは1になります。
このように、演算子の順序に迷ってしまうような場合には、公式のドキュメントの演算子の優先順位の項目を12.4.1 Operator Precedenceを見て理解してみると良いと思います。
上記はドキュメントから抜粋した演算子の優先順位で、上から順に優先的に実行されます。横並びになっている場合は左から順に実行されるため、今回は=(comparison)
とIS
で同じ行にありますが、=(comparison)
のほうが先に評価されて、その後でIS
が評価されていることがわかります。
<=>演算子を使ってみる
<=>演算子は見た目が非常に宇宙船に似ている事から、Spaceship Operatorや、UFO演算子等と呼ばれることも多いですが、MySQLの公式のドキュメントでは「NULL-safe equal」や「NULL安全等価演算子」と呼ばれます。
ここでは、<=>を使って先ほどの比較をもう一度行ってみましょう。
1と1を比較した場合と、1と0を比較した場合は以下のようになります。
上記は、通常の比較と変わらないことがわかります。SELECT文で計算するときには、通常のクエリを書くときと同じで、,
でつないで一度に出力することができます。では続いて1とNULLの比較を行ってみましょう。
結果がNULLではなくて、0になっていることがわかります。先の項目でも説明しましたが、0でもNULLでも変わらないので、WHERE句で使用する分にはあまり意味が無いようにも思えますが、計算した結果を利用する場合には結果が変わるので、少しだけ注意が必要です。続いてNULLとNULLの比較を行ってみましょう。
結果が通常の=
での比較と結果が変わって、1になっていることがわかります。このように、NULL以外の値は=演算子と同様に比較し、NULLが入ってきた場合にはIS NULL
と同じような挙動をしてくれる機能になります。
こちらは非常に便利な演算子です。特に最近では自作することはあまりないと思いますが、クエリビルダーなどを作る際に、NULLとそれ以外で挙動を変える必要がなくなるので、シンプルに記載することができるようになります。
IS NOT NULL
を表すにはどうしたら良いのか?という話はあると思いますが、結果はただの真偽値なので、NOT演算子と組み合わせて以下のように表せます。
ただし、これはMySQL固有の記法です。他のRDBMSを利用する場合などには混乱の元となってしまうため、利用しないほうが良いかもしれません。
まとめ
今回はMySQLの固有の機能である<=>演算子について紹介しました。<=>演算子を使うと、NULL値も他の値と同様に比較することができるため、扱いが非常に容易になります。
特にNULLが来るかどうかよくわからない値のプレースホルダーと組み合わせて使用すると、NULL値とそれ以外でISと=での使い分けを考えずに済むため、非常に簡単にクエリを組み立てることができます。
ただ、こんなに便利な<=>演算子ですが、こちらSQL標準のものではないため、MySQL以外のRDBMSへの移行を行う場合や、MySQL以外のRDBMSを組み合わせて使用する場合には、非常に厄介なものになると思います。用法用量をよく守って便利に使用していきましょう。