アドベントカレンダー10日目です！
MySQLに特化した内容ではありませんが、最近NULLの比較でハマったので知識の整理も兼ねて書き残します。
NULLと比較演算
NULLと比較したらどうなるか？
NULLとの比較結果は常にNULLです。
SQLの比較演算の結果は1(true)・ 0(false)・NULLの3種類です。
NULLと比較した場合、人間の目で見た真偽とは関係なく結果はNULLになります。
SELECT 'a' = 'a';
-- 1(true)
SELECT 'b' = 'a';
-- 0(false)
SELECT '' = 'a';
-- 0(false)
SELECT NULL = 'a';
-- NULL, 偽に見えるが0(false)ではない
SELECT NULL = NULL;
-- NULL, 真に見えるが1(true)ではない
結果をtrue/falseで受け取るには？
演算の結果を1(true)・0(false)で受け取るためにはIS NULLやIS NOT NULLなどを使用します。
SELECT NULL IS NULL;
-- 1(true)
SELECT NULL IS NOT NULL;
-- 0(false)
また、<=>(NULL安全等価演算子)を使用する方法もあります。
NULL以外の値の比較では=(等価演算子)と同じ挙動ですが、NULL同士の比較であれば1(true)が、片方のみNULLであれば0(false)の結果が返ります。
SELECT 'a' <=> 'a', 'b' <=> 'a', '' <=> 'a';
-- 1(true), 0(false), 0(false)
SELECT NULL <=> NULL;
-- 1(true)
SELECT NULL <=> 'a';
-- 0(false)
ハマったこと
<>(不等価演算子)やNOT演算を利用した場合、比較対象のカラムにNULLが含まれると意図しない挙動が発生します。
実際にテーブルを用意して検証します。
テーブル・レコードの生成
mysql> CREATE TABLE users(id int, name varchar(10));
mysql> INSERT INTO users VALUES(1, 'taro'), (2, 'hanako'), (3, ''), (4, NULL);
mysql> SELECT * FROM users;
+------+--------+
| id | name |
+------+--------+
| 1 | taro |
| 2 | hanako |
| 3 | |
| 4 | NULL |
+------+--------+
SELECT文を実行
<>(不等価演算子)もしくはNOT演算を利用してname = 'taro'以外のレコードを取得します。
id = 2, 3, 4のレコードが取得できればOKです。
NG
id = 4のレコードが取得できません。
抽出対象はname <> 'taro'の結果が1(true)のレコードのみです。
id = 4のレコードはnameの値がNULLなのでname <> 'taro'の結果がNULLになり、抽出から除外されます。
mysql> SELECT * FROM users WHERE name <> 'taro';
-- SELECT * FROM users WHERE NOT(name = 'taro')も同様
+------+--------+
| id | name |
+------+--------+
| 2 | hanako |
| 3 | |
+------+--------+
OK
id = 4のレコードも含めて取得できました。
IS NULLの条件追加によりnameの値がNULLの場合も1(ture)が得られ、抽出対象となります。
mysql> SELECT * FROM users WHERE name <> 'taro' OR name IS NULL;
+------+--------+
| id | name |
+------+--------+
| 2 | hanako |
| 3 | |
| 4 | NULL |
+------+--------+
ハマらないためにできること
SQLを実行する度にNULLを想定するのではなく、カラムの定義で解決できれば楽ですね。
- カラムにNOT NULL制約を追加する
- カラムにデフォルト値を設定する
- 常にNULLを想定したSQLを作成する
参考
採用PR
弊社で一緒に働く仲間を募集しています。
全てのオタクを幸せにしたい方、是非ご覧ください！
アドベントカレンダーも中盤に差し掛かりました。
クリスマスまで楽しんでくださいね！