MySQL NULL値処理の詳細

nullについて話す前に、例のテーブルデータを見てみましょう.

3306>select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | chen  |
|    2 | zhang |
|    3 | NULL  |
+------+-------+
3 rows in set (0.00 sec)

そして、「chen」という名前の他の行の記録をすべて調べたいと思っています.私たちはこのように書く可能性があります.

3306>select * from t1 where name!='chen';
+------+-------+
| id   | name  |
+------+-------+
|    2 | zhang |
+------+-------+
1 row in set (0.00 sec)

しかし、結果はあまり私たちの望み通りではないようで、idが1の記録は検出されていません.nullは「chen」と等しくないに違いありません.どうして調べられないのですか.
NUll値は初心者にとって非常に混同されやすく、nullと空の文字列''が同じだと思われることが多いが、実際には両者は非常に異なり、NULLは「a missing unknown value」を表し、文字列''は確定した値であり、本質的には異なる.
例を挙げます.

mysql> INSERT INTO t_user (phone) VALUES (NULL);
mysql> INSERT INTO t_user (phone) VALUES ('');

第1条insert文はこの携帯電話の番号がまだどれだけなのか分からないと思って、a missing unknown phone number第2条はこの携帯電話の番号が確定したことを代表して、その携帯電話の番号は空の文字列です
nullに関連する比較を処理するために、mysqlではis null、is not null、<=>、isnull()を使用して比較をテストできます.=など、null値と比較するテストは使用できません.nullとの比較および関数演算の結果はnullです.
次の3つの例で問題を説明します.

   null       null
3306>SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);
+------+--------+--------------------------+
| NULL | 1+NULL | CONCAT('Invisible',NULL) |
+------+--------+--------------------------+
| NULL |   NULL | NULL                     |
+------+--------+--------------------------+
1 row in set (0.00 sec)

3306>SELECT 1>NULL, 1=NULL, 1!=NULL;
+--------+--------+---------+
| 1>NULL | 1=NULL | 1!=NULL |
+--------+--------+---------+
|   NULL |   NULL |    NULL |
+--------+--------+---------+
1 row in set (0.00 sec)

            0 1
3306 > SELECT 1 IS NULL, 1 IS NOT NULL, 1<=>NULL,NULL<=>NULL;
+-----------+---------------+----------+-------------+
| 1 IS NULL | 1 IS NOT NULL | 1<=>NULL | NULL<=>NULL |
+-----------+---------------+----------+-------------+
|         0 |             1 |        0 |           1 |
+-----------+---------------+----------+-------------+
1 row in set (0.00 sec)

しかし、何事にも例外があります.distinct、group by、order byを使用すると、異なるnull値が等しく計算されます.
最も重要なのはmysqlでは0とnullが偽を表し、他の値は真を表す.
だから最初の例を振り返ってみると、私たちのwhere条件がname!='chen'の場合、id=1の行と比較されます.すなわち、テスト:'chen'!='chen'このテスト結果は0,偽であるため,id=1の行は検出されない.次にテストします:'zhang'!='chen'このテスト結果は1であり,真であるためid=2の行が検出される.次にテストします:NULL!='chen'このテスト結果はNULLで偽なのでid=3の行は検出されません.したがって,最終的な結果はid=2行のデータのみが検出された.もし私たちの目的地に着いたらどうしますか?

3306>select * from t1 where name!='chen' or name is null;
+------+-------+
| id   | name  |
+------+-------+
|    2 | zhang |
|    3 | NULL  |
+------+-------+
2 rows in set (0.01 sec)

 

3306>select * from t1 where ifnull(name,0)!='chen' ;
+------+-------+
| id   | name  |
+------+-------+
|    2 | zhang |
|    3 | NULL  |
+------+-------+
2 rows in set (0.00 sec)

第一の方法をお勧めします.mysqlはnull列の検索にクエリー最適化があるためです.

3306>explain select * from test a where a.code='AAAAAA' or a.code is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ref_or_null
possible_keys: code
          key: code
      key_len: 21
          ref: const
         rows: 73603
        Extra: Using index condition
1 row in set (0.01 sec)

2つ目は、カラムに関数を加えたクエリではインデックスが機能しません.
その他の注意事項

order by...ascではnull値が一番前に置かれ、order by...descの場合、nullは最も後ろに置かれ、nullが無限の値であることに相当します.

集約関数、例えばcount()、min()、sum()はnull値を無視する.唯一の例外はcount()、例えばSELECT COUNT()、COUNT(age)FROM personである.上記の文が最初のcount(*)はpersonテーブルの合計行数を計算し、2番目はpersonテーブルage列のnull以外の行数を計算する

である.

MyISAM、InnoDBおよびMEMORYエンジンではnull値を含むカラムにインデックスを追加できますが、他のエンジンではnot nullと宣言してインデックス

を追加する必要があります.

load data infileを使用する場合、空またはないカラムはnull値をインポートするには、データファイルにNで表す必要があります.

timestampにnull値を挿入すると、現在の時間が挿入され、1つの自増列がnull値を挿入すると、次の自増値が挿入されます.