MySQL IS NULL判断值是否为NULL及优化详解

摘要：在本教程中，您将学习如何使用MySQL IS NULL运算符来判断值是否为NULL。

MySQL IS NULL运算符

要判断值是否为 NULL ，请使用 IS NULL 运算符。以下是 IS NULL 运算符的基本语法：

 value IS NULL

如果值为 NULL ，则表达式返回true。否则，它返回false。

注意MySQL没有内置 BOOLEAN 类型。它使用 TINYINT(1) 来表示 BOOLEAN 的值，如true表示1，flase表示0。

因为 IS NULL 是一个比较运算符，所以其他任何运算符使用的地方都可以使用 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 IS NULL示例

我们将使用示例数据库中的 customers 表进行实例演示。

要获得没有销售代表的客户，请使用 IS NULL运算符，如下所示：

 SELECT 
    customerName, 
    country, 
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NULL
ORDER BY customerName;

结果如下图所示：

要获得具有销售代表的客户，您需要使用 IS NOT NULL 运算符：

 SELECT 
    customerName, 
    country, 
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NOT NULL
ORDER BY customerName;

结果如下图所示：

MySQL IS NULL的特殊功能

为了与ODBC程序兼容，MySQL支持 IS NULL 操作符的一些特殊功能。

1）如果 DATE 或 DATETIME类型的字段具有 NOT NUL 约束并且包含特殊日期如 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;

以上SQL代码创建了一个名为 projects 的表，并将一些数据插入到表中。最后一个sql语句使用了 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在col_name IS NULL 时做和 col_name = constant_value 一样的优化。例如，MySQL使用索引或者范围来根据 IS NUL L搜索 NULL。

 SELECT * FROM tbl_name WHERE key_col IS NULL; 
SELECT * FROM tbl_name WHERE key_col <=> NULL; 
SELECT * FROM tbl_name 
WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

如果一个 WHERE 子句包括了一个 col_name IS NULL 条件，并且这个字段声明为 NOT NULL，那么这个表达式就会被优化。当字段可能无论如何都会产生 NULL 值时，就不会再做优化了;例如，当它来自一个 LEFT JOIN 中右边的一个表时。

MySQL 4.1.1或更高会对连接 col_name = expr AND col_name IS NULL 做额外的优化，常见的就是子查询。EXPLAIN 当优化起作用时会显示 ref_or_null。

优化程序会为任何索引部分处理 IS NULL。

以下几个例子中都做优化了，假使字段 a 和表 t2 中 b 有索引了：

 SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL; 
SELECT * FROM t1,t2 WHERE t1.a=t2.a OR t2.a IS NULL; 
SELECT * FROM t1,t2 
WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b; 
SELECT * FROM t1,t2 
WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL); 
SELECT * FROM t1,t2 
WHERE (t1.a=t2.a AND t2.a IS NULL AND ...) 
OR (t1.a=t2.a AND t2.a IS NULL AND ...);

请注意，优化程序只会处理一个 IS NULL 级别。下面的查询中，MySQL只会使用键来查询表达式 (t1.a=t2.a AND t2.a IS NULL) 而无法使在 b 上使用索引部分： ref_or_null 首先读取引用键，然后独立扫描键值为 NULL 的记录。

 SELECT * FROM t1,t2 
WHERE (t1.a=t2.a AND t2.a IS NULL) 
OR (t1.b=t2.b AND t2.b IS NULL);

在本教程中，您已经学习了如何使用MySQL IS NULL运算符来判断值是否为 NULL 。

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did225697

更新时间：2023-05-11 阅读：45次