在本教學中，您將學習如何使用MySQL IS NULL運算子來測試值是否為一個NULL值。
要測試值是否為NULL值，需要使用IS NULL運算子。 以下顯示IS NULL運算子的語法：
value IS NULL
如果值為NULL，該表示式將返回true。 否則返回false。
請注意，MySQL沒有內建的BOOLEAN型別。 它使用TINYINT(1)來表示BOOLEAN值，即1表示true，0表示false。
因為IS NULL是一個比較運算子，所以您可以在任何使用運算子的地方使用它，例如在SELECT或WHERE子句中。如下面的例子：
SELECT 1 IS NULL, # -- 0
0 IS NULL, # -- 0
NULL IS NULL; # -- 1;
要檢查值是否不為NULL，請使用IS NOT NULL運算子，如下所示：
value IS NOT NULL
如果該值不為NULL，則此表示式返回true(也就是1)。 否則返回false(也就是0)。 請考慮以下範例：
SELECT 1 IS NOT NULL, #-- 1
0 IS NOT NULL, #-- 1
NULL IS NOT NULL; #-- 0;
上面查詢語句，執行後得到以下結果 -
mysql> SELECT 1 IS NOT NULL, #-- 1
0 IS NOT NULL, #-- 1
NULL IS NOT NULL; #-- 0
+---------------+---------------+------------------+
| 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL |
+---------------+---------------+------------------+
| 1 | 1 | 0 |
+---------------+---------------+------------------+
1 row in set
我們將使用範例資料庫(yiibaidb)中的customers表進行演示，customers表的結構如下所示 -
mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber | int(11) | NO | PRI | NULL | |
| customerName | varchar(50) | NO | | NULL | |
| contactLastName | varchar(50) | NO | | NULL | |
| contactFirstName | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
| addressLine1 | varchar(50) | NO | | NULL | |
| addressLine2 | varchar(50) | YES | | NULL | |
| city | varchar(50) | NO | | NULL | |
| state | varchar(50) | YES | | NULL | |
| postalCode | varchar(15) | YES | | NULL | |
| country | varchar(50) | NO | | NULL | |
| salesRepEmployeeNumber | int(11) | YES | MUL | NULL | |
| creditLimit | decimal(10,2) | YES | | NULL | |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set
要查詢沒有銷售代表的客戶，請使用IS NULL運算子，如下所示：
SELECT
customerName,
country,
salesrepemployeenumber
FROM
customers
WHERE
salesrepemployeenumber IS NULL
ORDER BY customerName;
執行上面查詢，得到以下結果 -
mysql> SELECT
customerName,
country,
salesrepemployeenumber
FROM
customers
WHERE
salesrepemployeenumber IS NULL
ORDER BY customerName;
+--------------------------------+--------------+------------------------+
| customerName | country | salesrepemployeenumber |
+--------------------------------+--------------+------------------------+
| ANG Resellers | Spain | NULL |
| Anton Designs, Ltd. | Spain | NULL |
| Asian Shopping Network, Co | Singapore | NULL |
| Asian Treasures, Inc. | Ireland | NULL |
| BG&E Collectables | Switzerland | NULL |
| Cramer Spezialitten, Ltd | Germany | NULL |
| Der Hund Imports | Germany | NULL |
| Schuyler Imports | Netherlands | NULL |
| Stuttgart Collectable Exchange | Germany | NULL |
| Warburg Exchange | Germany | NULL |
... ...
+--------------------------------+--------------+------------------------+
22 rows in set
要查詢有銷售代表的客戶，請使用IS NOT NULL運算子，如下查詢語句 -
SELECT
customerName,
country,
salesrepemployeenumber
FROM
customers
WHERE
salesrepemployeenumber IS NOT NULL
ORDER BY customerName;
執行上面查詢，得到以下結果 -
mysql> SELECT
customerName,
country,
salesrepemployeenumber
FROM
customers
WHERE
salesrepemployeenumber IS NOT NULL
ORDER BY customerName;
+------------------------------------+-------------+------------------------+
| customerName | country | salesrepemployeenumber |
+------------------------------------+-------------+------------------------+
| Alpha Cognac | France | 1370 |
| American Souvenirs Inc | USA | 1286 |
| Amica Models & Co. | Italy | 1401 |
| Anna's Decorations, Ltd | Australia | 1611 |
| Atelier graphique | France | 1370 |
| Australian Collectables, Ltd | Australia | 1611 |
| Australian Collectors, Co. | Australia | 1611 |
| Australian Gift Network, Co | Australia | 1611 |
| Auto Associs & Cie. | France | 1370 |
| Auto Canal+ Petit | France | 1337 |
| Auto-Moto Classics Inc. | USA | 1216 |
| AV Stores, Co. | UK | 1501 |
| Baane Mini Imports | Norway | 1504 |
| Bavarian Collectables Imports, Co. | Germany | 1504 |
... ...
+------------------------------------+-------------+------------------------+
100 rows in set
為了相容ODBC程式，MySQL支援IS NULL運算子的一些專門功能。
(1). 如果具有NOT NULL約束的DATE或DATETIME列包含特殊日期’0000-00-00‘，則可以使用IS NULL運算子來查詢這些行。如下範例 -
CREATE TABLE IF NOT EXISTS projects (
id INT AUTO_INCREMENT,
title VARCHAR(255),
begin_date DATE NOT NULL,
complete_date DATE NOT NULL,
PRIMARY KEY(id)
);
INSERT INTO projects(title,begin_date, complete_date)
VALUES('New CRM','2020-01-01','0000-00-00'),
('ERP Future','2020-01-01','0000-00-00'),
('VR','2020-01-01','2030-01-01');
SELECT
*
FROM
projects
WHERE
complete_date IS NULL;
在這個例子中，建立了一個 projects 新表，並將一些資料插入到表中。最後一個查詢使用IS NULL來獲取complete_date列中的值為「0000-00-00」的行。
(2). 如果變數@@sql_auto_is_null設定為1，則可以使用IS NULL運算子在執行INSERT語句後獲取生成列的值。請注意，預設情況下，變數@@sql_auto_is_null為0。請參見以下範例。
首先，將變數@@sql_auto_is_null設定為1。
SET @@sql_auto_is_null = 1;
第二步，在projects表中插入一個新行：
INSERT INTO projects(title,begin_date, complete_date)
VALUES('MRP III','2010-01-01','2020-12-31');
第三步，使用IS NULL運算子來獲取id列的生成值：
SELECT
id
FROM
projects
WHERE
id IS NULL;
MySQL對於IS NULL運算子執行相同的優化方式與等於(=)運算子相同。
例如，MySQL在使用IS NULL運算子搜尋NULL時使用索引，如以下查詢所示：
SELECT
customerNumber,
salesRepEmployeeNumber
FROM
customers
WHERE
salesRepEmployeeNumber IS NULL;
檢視EXPLAIN查詢過程：
EXPLAIN SELECT
customerNumber,
salesRepEmployeeNumber
FROM
customers
WHERE
salesRepEmployeeNumber IS NULL;
執行上面查詢語句，輸出以下結果 -
mysql> EXPLAIN SELECT
customerNumber,
salesRepEmployeeNumber
FROM
customers
WHERE
salesRepEmployeeNumber IS NULL;
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | customers | NULL | ref | salesRepEmployeeNumber | salesRepEmployeeNumber | 5 | const | 22 | 100 | Using where; Using index |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
1 row in set
MySQL也可以優化組合col = value OR col IS NULL。 請參閱以下範例：
EXPLAIN SELECT
customerNumber,
salesRepEmployeeNumber
FROM
customers
WHERE
salesRepEmployeeNumber = 1370 OR
salesRepEmployeeNumber IS NULL;
執行上面查詢語句，得到以下結果 -
mysql> EXPLAIN SELECT
customerNumber,
salesRepEmployeeNumber
FROM
customers
WHERE
salesRepEmployeeNumber = 1370 OR
salesRepEmployeeNumber IS NULL;
+----+-------------+-----------+------------+-------------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | customers | NULL | ref_or_null | salesRepEmployeeNumber | salesRepEmployeeNumber | 5 | const | 29 | 100 | Using where; Using index |
+----+-------------+-----------+------------+-------------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
1 row in set
在這個例子中，當應用優化時，EXPLAIN會顯示ref_or_null。
如果您有一個列的組合鍵，MySQL可以對任何關鍵部分執行優化。假設在表t1的列c1和c2上有一個索引，以下查詢被優化：
SELECT
*
FROM
t1
WHERE
c1 IS NULL;
在本教學中，您已經學習了如何使用MySQL IS NULL運算子來測試值是否為NULL。