在本教程中,您将学习如何使用MySQL IS NULL
运算符来测试值是否为一个NULL
值。
1. MySQL IS 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
2. MySQL IS NULL示例
我们将使用示例数据库(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
3. MySQL IS NULL的专用功能
为了兼容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;
4. MySQL 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
。