等待下一个秋

  • Spark
  • Flink
  • Hive
  • 数据仓库
  • 收徒弟
  • Java
    • Spring
    • Mybatis
    • SpringBoot
    • 面试题
  • Python
    • Python基础
    • 爬虫
    • Numpy
    • matplotlib
    • Flask
  • 知识星球
  • 技术杂谈
    • Linux知识
    • Docker
    • Git教程
    • Redis教程
    • mysql
    • 前端
    • R语言
    • 机器学习
  • 其它
    • 副业挣钱
    • 关于我
    • 资料下载
    • 资料文档
专注于Hadoop/Spark/Flink/Hive/数据仓库等
关注公众号:大数据技术派,获取更多学习资料。
  1. 首页
  2. 技术杂谈
  3. mysql
  4. 正文

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

2021年10月13日 337点热度 0人点赞 0条评论

摘要:在本教程中,您将学习如何使用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。


标签: mysql
最后更新:2021年10月13日

等待下一个秋

待我代码写成,便娶你为妻!专注于Hadoop/Spark/Flink/Hive/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

打赏 点赞
< 上一篇
下一篇 >

文章评论

取消回复

等待下一个秋

待我代码写成,便娶你为妻!专注于Hadoop/Spark/Flink/Hive/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

搜一搜
微信
最新 热点 随机
最新 热点 随机
logstash同步mysql数据到elasticsearch Spring IOC 容器源码分析 elasticsearch修改字段类型 curl操作elasticsearch常用命令 Python通过orm操作mysql数据库 Python进程管理——Supervisor
Spring IOC 容器源码分析logstash同步mysql数据到elasticsearch
Matplotlib 标注 MySQL LIMIT实例及取前N行数据 Spark内核解析 彻底解决Hive小文件问题 黑客与画家 11.百年后的编程语言 第一次赚钱经历,大三时录制在线课程,每月收入3千,生活费是够了?
标签聚合
Flink Hive 挣钱 Python 数据仓库 mysql Redis 书籍 大数据 R语言 Java 算法
文章归档
  • 2022年7月
  • 2022年6月
  • 2022年5月
  • 2022年4月
  • 2022年3月
  • 2022年2月
  • 2022年1月
  • 2021年12月
  • 2021年11月
  • 2021年10月
  • 2021年9月
  • 2021年8月
  • 2021年6月
  • 2021年5月
  • 2021年4月
  • 2021年3月
  • 2021年2月
  • 2021年1月
  • 2020年12月
  • 2020年11月
  • 2020年10月
  • 2020年9月
  • 2020年8月
  • 2020年7月
  • 2020年5月
  • 2020年4月
  • 2020年1月
  • 2019年9月
  • 2019年8月
  • 2019年7月
  • 2019年6月
  • 2019年5月
  • 2019年4月
  • 2019年3月
  • 2019年1月
  • 2018年12月
  • 2017年5月

©2022 ikeguang.com. 保留所有权利。

鄂ICP备2020019097号-1

鄂公网安备 42032202000160号