MySQLでのNull、空文字、関連する関数など
公式ドキュメントでは
いきなり、ちょっとキツ目の表現でスタートします。
NULL 値の概念については、NULL が空の文字列 '' と同じであると考えがちな SQL の初心者が混乱することがよくあります。これらは同一ではありません。たとえば、次の 2 つのステートメントは完全に異なります。
mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
両方のステートメントで phone カラムに値が挿入されていますが、最初のステートメントは NULL 値を挿入しており、2 番目のステートメントは空の文字列を挿入しています。最初のステートメントの意味は「電話番号がわからない」、2 番目のステートメントの意味は「この人は電話を持っていないため、電話番号がない」と見なすことができます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: B.5.5.3 NULL 値に関する問題
それでは実験
まずは準備
いつもどおり、環境はLinux上のMySQL 5.6.31です。
こんな感じでテーブルとデータを用意しておきます。
mysql> CREATE DATABASE test_null DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ; Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE `test_tbl` ( -> `col_id` char(1) COLLATE utf8mb4_bin NOT NULL, -> `col_char1` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; Query OK, 0 rows affected (0.17 sec) mysql> show create table test_tbl\G *************************** 1. row *************************** Table: test_tbl Create Table: CREATE TABLE `test_tbl` ( `col_id` char(1) COLLATE utf8mb4_bin NOT NULL, `col_char1` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) mysql> INSERT INTO test_tbl (col_id,col_char1) values -> ('a','a'), -> ('b','bb'), -> ('c','ccc'), -> ('d','dddd'), -> ('e','eeeee'), -> ('f',''), -> ('g',' '), -> ('h',' '), -> ('i',' '), -> ('j',' '), -> ('k',' '), -> ('l',null), -> ('m','m '), -> ('n','nn '), -> ('o','ooo '), -> ('p','pppp '); Query OK, 16 rows affected (0.03 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> SELECT col_id,length(col_char1) FROM test_tbl; +--------+-------------------+ | col_id | length(col_char1) | +--------+-------------------+ | a | 1 | | b | 2 | | c | 3 | | d | 4 | | e | 5 | | f | 0 | | g | 1 | | h | 2 | | i | 3 | | j | 4 | | k | 5 | | l | NULL | | m | 5 | | n | 5 | | o | 5 | | p | 5 | +--------+-------------------+ 16 rows in set (0.00 sec)
スペースだけを詰め込んでも、ちゃんと桁数として認識しています。
ここ、あとで重要ですので、今後の検証用SQLも、LENGTH結果を一緒に表示しておきたいと思います。
ISNULL関数
ISNULL(expr)
expr が NULL の場合、ISNULL() は 1 を返し、それ以外の場合は 0 を返します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.3.2 比較関数と演算子
ということで、明示的にNullをInsertしたのは1行だけですから、その行だけが1＝Trueとして認識されることが期待されます。
mysql> SELECT col_id,col_char1,ISNULL(col_char1),LENGTH(col_char1) FROM test_tbl; +--------+-----------+-------------------+-------------------+ | col_id | col_char1 | ISNULL(col_char1) | LENGTH(col_char1) | +--------+-----------+-------------------+-------------------+ | a | a | 0 | 1 | | b | bb | 0 | 2 | | c | ccc | 0 | 3 | | d | dddd | 0 | 4 | | e | eeeee | 0 | 5 | | f | | 0 | 0 | | g | | 0 | 1 | | h | | 0 | 2 | | i | | 0 | 3 | | j | | 0 | 4 | | k | | 0 | 5 | | l | NULL | 1 | NULL | | m | m | 0 | 5 | | n | nn | 0 | 5 | | o | ooo | 0 | 5 | | p | pppp | 0 | 5 | +--------+-----------+-------------------+-------------------+ 16 rows in set (0.00 sec)
期待通りです。
長さ0の文字列「''」を入れた行は、Nullではない、と認識されています。
IFNULL関数
IFNULL(expr1,expr2)
expr1 が NULL でない場合、IFNULL() は expr1 を返し、それ以外の場合は expr2 を返します。IFNULL() は、使用されているコンテキストに応じて、数値または文字列値を返します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.4 制御フロー関数
ということで、明示的にNullをInsertしたのは1行だけですから、その行だけが1＝Trueとして認識され、変換後の文字列が表示されることが期待されます。
mysql> SELECT col_id,col_char1,IFNULL(col_char1,'null_value!'),LENGTH(col_char1) FROM test_tbl; +--------+-----------+---------------------------------+-------------------+ | col_id | col_char1 | IFNULL(col_char1,'null_value!') | LENGTH(col_char1) | +--------+-----------+---------------------------------+-------------------+ | a | a | a | 1 | | b | bb | bb | 2 | | c | ccc | ccc | 3 | | d | dddd | dddd | 4 | | e | eeeee | eeeee | 5 | | f | | | 0 | | g | | | 1 | | h | | | 2 | | i | | | 3 | | j | | | 4 | | k | | | 5 | | l | NULL | null_value! | NULL | | m | m | m | 5 | | n | nn | nn | 5 | | o | ooo | ooo | 5 | | p | pppp | pppp | 5 | +--------+-----------+---------------------------------+-------------------+ 16 rows in set (0.00 sec)
これも期待通り。
NULLIF関数
ちょっと特殊な関数ですね。
expr1もexpr2も、Nullであるかどうかは関係がなく、その評価結果としてNullを返す、というものです。
NULLIF(expr1,expr2)
expr1 = expr2 が true の場合は NULL を返し、それ以外の場合は expr1 を返します。これは、CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END と同じです。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.4 制御フロー関数
ここの評価にNullや長さ0の文字列「''」を入れたら、どうなるでしょうか。
mysql> SELECT col_id,col_char1,NULLIF(col_char1,null),LENGTH(col_char1) FROM test_tbl; +--------+-----------+------------------------+-------------------+ | col_id | col_char1 | NULLIF(col_char1,null) | LENGTH(col_char1) | +--------+-----------+------------------------+-------------------+ | a | a | a | 1 | | b | bb | bb | 2 | | c | ccc | ccc | 3 | | d | dddd | dddd | 4 | | e | eeeee | eeeee | 5 | | f | | | 0 | | g | | | 1 | | h | | | 2 | | i | | | 3 | | j | | | 4 | | k | | | 5 | | l | NULL | NULL | NULL | | m | m | m | 5 | | n | nn | nn | 5 | | o | ooo | ooo | 5 | | p | pppp | pppp | 5 | +--------+-----------+------------------------+-------------------+ 16 rows in set (0.00 sec)
Nullと比較した場合、NullをInsertした1行だけをNullとしてを正しく検出しています。
mysql> SELECT col_id,col_char1,NULLIF(col_char1,''),LENGTH(col_char1) FROM test_tbl; +--------+-----------+----------------------+-------------------+ | col_id | col_char1 | NULLIF(col_char1,'') | LENGTH(col_char1) | +--------+-----------+----------------------+-------------------+ | a | a | a | 1 | | b | bb | bb | 2 | | c | ccc | ccc | 3 | | d | dddd | dddd | 4 | | e | eeeee | eeeee | 5 | | f | | NULL | 0 | | g | | NULL | 1 | | h | | NULL | 2 | | i | | NULL | 3 | | j | | NULL | 4 | | k | | NULL | 5 | | l | NULL | NULL | NULL | | m | m | m | 5 | | n | nn | nn | 5 | | o | ooo | ooo | 5 | | p | pppp | pppp | 5 | +--------+-----------+----------------------+-------------------+ 16 rows in set (0.01 sec)
しかし、長さ0の文字列''と比較させた場合、以下のものがごっちゃになっています。
- Nullを入れたもの。
- 長さ0の文字列''を入れたもの。
- 半角スペースだけを入れたもの。
ちょっと困りましたね。
Nullと''の判別がつかないだけでなく、半角スペースは全部トリミングしてしまっているようです。
公式ドキュメントに「CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 ENDと同じ」っていう記載がありましたね。
なので、演算子の動きも確認しておく必要がありそうです。。
冒頭で参照したブログで扱っている「=」、「!=」の他に、「<=>」という演算子もあるので、それらを確認することにします。
「=」演算子と「!=」演算子
mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = ''; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | f | | 0 | | g | | 1 | | h | | 2 | | i | | 3 | | j | | 4 | | k | | 5 | +--------+-----------+-------------------+ 6 rows in set (0.00 sec)
いきなり大本命ですが、長さ0の文字列「''」～半角スペースだけでフル桁がヒットしています。
つまり、半角スペースは、何桁保存されていても、比較時にトリミングされてしまっているような挙動となります。
一方、Nullはヒットしません。
mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 != ''; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | a | a | 1 | | b | bb | 2 | | c | ccc | 3 | | d | dddd | 4 | | e | eeeee | 5 | | m | m | 5 | | n | nn | 5 | | o | ooo | 5 | | p | pppp | 5 | +--------+-----------+-------------------+ 9 rows in set (0.00 sec)
Nullないしスペースのみが格納されたもの以外が抽出されました。
念のため、半角スペースを検出できるか、半角スペースの桁数ごとの違いを認識できるか、チェックしておきます。
mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | f | | 0 | | g | | 1 | | h | | 2 | | i | | 3 | | j | | 4 | | k | | 5 | +--------+-----------+-------------------+ 6 rows in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | f | | 0 | | g | | 1 | | h | | 2 | | i | | 3 | | j | | 4 | | k | | 5 | +--------+-----------+-------------------+ 6 rows in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | f | | 0 | | g | | 1 | | h | | 2 | | i | | 3 | | j | | 4 | | k | | 5 | +--------+-----------+-------------------+ 6 rows in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | f | | 0 | | g | | 1 | | h | | 2 | | i | | 3 | | j | | 4 | | k | | 5 | +--------+-----------+-------------------+ 6 rows in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | f | | 0 | | g | | 1 | | h | | 2 | | i | | 3 | | j | | 4 | | k | | 5 | +--------+-----------+-------------------+ 6 rows in set (0.00 sec)
残念、まったく区別できませんでした。
この仕様についての回避策については、「BINARY」句をつければいいって、先人の方が言っておりました。
qiita.com
mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY ''; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | f | | 0 | +--------+-----------+-------------------+ 1 row in set (0.01 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | g | | 1 | +--------+-----------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | h | | 2 | +--------+-----------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | i | | 3 | +--------+-----------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | j | | 4 | +--------+-----------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY ' '; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | k | | 5 | +--------+-----------+-------------------+ 1 row in set (0.00 sec)
はい。全部区別できました。
「<=>」演算子
あまり見慣れない演算子です。「NULL 安全等価演算子」って呼ぶらしいです。
<=>
NULL - 安全等価。この演算子では、= 演算子のように等価比較が実行されますが、両方のオペランドが NULL であれば、NULL でなく 1 が返され、一方のオペランドが NULL の場合は、NULL でなく 0 が返されます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.3.2 比較関数と演算子
mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 <=> ''; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | f | | 0 | | g | | 1 | | h | | 2 | | i | | 3 | | j | | 4 | | k | | 5 | +--------+-----------+-------------------+ 6 rows in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 <=> NULL; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | l | NULL | NULL | +--------+-----------+-------------------+ 1 row in set (0.00 sec)
Nullと長さ0の文字列''を明確に区別してくれていますね。
ただし、半角スペースは、例によってトリミングされてしまうのです。
「IS NULL」と「IS NOT NULL」演算子
Nullの評価と言ったら、これですよね。
mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 IS NULL; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | l | NULL | NULL | +--------+-----------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 IS NOT NULL; +--------+-----------+-------------------+ | col_id | col_char1 | LENGTH(col_char1) | +--------+-----------+-------------------+ | a | a | 1 | | b | bb | 2 | | c | ccc | 3 | | d | dddd | 4 | | e | eeeee | 5 | | f | | 0 | | g | | 1 | | h | | 2 | | i | | 3 | | j | | 4 | | k | | 5 | | m | m | 5 | | n | nn | 5 | | o | ooo | 5 | | p | pppp | 5 | +--------+-----------+-------------------+ 15 rows in set (0.00 sec)
期待通り、NullとしてInsertした1行だけを、明確に区別してくれています。
まとめ
- Nullと長さ0の文字列「''」は別物。
- Nullを評価する必要がある場合、「=」、「!=」は使わない！
- 長さ0の文字列「''」の評価にも、「=」、「!=」は使わないほうが安全。Nullと区別できないから。使うなら「BINARY」を使って！
- 内部的に「=」、「!=」を使っている関数がある。ここにNullや長さ0の文字列「''」を評価させない！
- 作者: 鈴木啓修,山田奈緒子
- 出版社/メーカー: 技術評論社
- 発売日: 2014/12/18
- メディア: 単行本（ソフトカバー）
- この商品を含むブログ (3件) を見る
