# 3.8 IS NULL 运算符 在本教程中，您将学习如何使用 MySQL `IS NULL`运算符来测试值是否为一个`NULL`值。 ## MySQL IS NULL 操作符简介 要测试值是否为`NULL`值，需要使用`IS NULL`运算符。 以下显示`IS NULL`运算符的语法： ```sql value IS NULL ``` 如果值为`NULL`，该表达式将返回`true`。 否则返回`false`。 请注意，MySQL 没有内置的[BOOLEAN类型](http://www.yiibai.com/mysql/boolean.html)。 它使用[TINYINT(1)](http://www.yiibai.com/mysql/int.html)来表示`BOOLEAN`值，即`1`表示`true`，`0`表示`false`。 因为`IS NULL`是一个比较运算符，所以您可以在任何使用运算符的地方使用它，例如在 [SELECT](http://www.yiibai.com/mysql/select-statement-query-data.html) 或 [WHERE](http://www.yiibai.com/mysql/where/) 子句中。如下面的例子： ```sql SELECT 1 IS NULL, # -- 0 0 IS NULL, # -- 0 NULL IS NULL; # -- 1; ``` 要检查值是否不为`NULL`，请使用`IS NOT NULL`运算符，如下所示： ```sql value IS NOT NULL ``` 如果该值不为`NULL`，则此表达式返回`true`(也就是`1`)。 否则返回`false`(也就是`0`)。 请考虑以下示例： ```sql SELECT 1 IS NOT NULL, #-- 1 0 IS NOT NULL, #-- 1 NULL IS NOT NULL; #-- 0; ``` 上面查询语句，执行后得到以下结果 - ```sql 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 ``` ## MySQL IS NULL 示例 我们将使用[示例数据库(yiibaidb)](http://www.yiibai.com/mysql/sample-database.html)中的`customers`表进行演示，`customers`表的结构如下所示 - ```sql 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`运算符，如下所示： ```sql SELECT customerName, country, salesrepemployeenumber FROM customers WHERE salesrepemployeenumber IS NULL ORDER BY customerName; ``` 执行上面查询，得到以下结果 - ```sql 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`运算符，如下查询语句 - ```sql SELECT customerName, country, salesrepemployeenumber FROM customers WHERE salesrepemployeenumber IS NOT NULL ORDER BY customerName; ``` 执行上面查询，得到以下结果 - ```sql 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 ``` ## MySQL IS NULL 的专用功能 为了兼容 ODBC 程序，MySQL 支持`IS NULL`运算符的一些专门功能。 (1). 如果具有`NOT NULL`约束的`DATE`或`DATETIME`列包含特殊日期’`0000-00-00`‘，则可以使用`IS NULL`运算符来查找这些行。如下示例 - ```sql 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` 新表，并将一些数据[插入到表中](http://www.yiibai.com/mysql/insert-statement.html)。最后一个查询使用`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`。 ```sql SET @@sql_auto_is_null = 1; ``` *第二步*，在`projects`表中插入一个新行： ```sql INSERT INTO projects(title,begin_date, complete_date) VALUES('MRP III','2010-01-01','2020-12-31'); ``` *第三步*，使用`IS NULL`运算符来获取`id`列的生成值： ```sql SELECT id FROM projects WHERE id IS NULL; ``` ## MySQL IS NULL优化 MySQL 对于`IS NULL`运算符执行相同的优化方式与等于(`=`)运算符相同。 例如，MySQL 在使用`IS NULL`运算符搜索`NULL`时使用索引，如以下查询所示： ```sql SELECT customerNumber, salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber IS NULL; ``` 查看`EXPLAIN`查询过程： ```sql EXPLAIN SELECT customerNumber, salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber IS NULL; ``` 执行上面查询语句，输出以下结果 - ```sql 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`。 请参阅以下示例： ```sql EXPLAIN SELECT customerNumber, salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber = 1370 OR salesRepEmployeeNumber IS NULL; ``` 执行上面查询语句，得到以下结果 - ```sql 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`上有一个索引，以下查询被优化： ```sql SELECT * FROM t1 WHERE c1 IS NULL; ``` 在本教程中，您已经学习了如何使用 MySQL `IS NULL`运算符来测试值是否为`NULL`。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://hezhiqiang-book.gitbook.io/mysql/di-yi-zhang/index-2/is-null-yun-suan-fu.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.