読者です 読者をやめる 読者になる 読者になる

なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLでのNull、空文字、関連する関数など

先人はいるけれど、調べてみた。

比較演算子については、こちらにまとまってますね。
d.hatena.ne.jp



公式ドキュメントでは

いきなり、ちょっとキツ目の表現でスタートします。

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の文字列「''」を評価させない!

MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル