IT编程 > 数据库 > Mysql

神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL !

15人参与2019-08-13

前言

  开心一刻 

     一个中国小孩参加国外的脱口秀节目,因为语言不通,于是找了一个翻译。
    主持人问:“who is your favorite singer ?”
    翻译:”你最喜欢哪个歌手啊 ?”
    小孩兴奋地回答:”michael jackson”
    翻译转身对主持人说:”迈克尔-杰克逊”
    主持人看着翻译:"你说什么 ?"
    电视机前的观众:"我怎么有点蒙?" 

null

  null 用于表示缺失的值或遗漏的未知数据,不是某种具体类型的值。数据表中的 null 值表示该值所处的字段为空,值为 null 的字段没有值,尤其要明白的是:null 值与 0 或者空字符串是不同的。

  两种 null

    这种说法大家可能会觉得很奇怪,因为 sql 里只存在一种 null 。然而在讨论 null 时,我们一般都会将它分成两种类型来思考:“未知”(unknown)和“不适用”(not applicable,inapplicable)。

    以“不知道戴墨镜的人眼睛是什么颜色”这种情况为例,这个人的眼睛肯定是有颜色的,但是如果他不摘掉眼镜,别人就不知道他的眼睛是什么颜色。这就叫作未知。而“不知道冰箱的眼睛是什么颜色”则属于“不适用”。因为冰箱根本就没有眼睛,所以“眼睛的颜色”这一属性并不适用于冰箱。“冰箱的眼睛的颜色”这种说法和“圆的体积”“男性的分娩次数”一样,都是没有意义的。平时,我们习惯了说“不知道”,但是“不知道”也分很多种。“不适用”这种情况下的 null ,在语义上更接近于“无意义”,而不是“不确定”。这里总结一下:“未知”指的是“虽然现在不知道,但加上某些条件后就可以知道”;而“不适用”指的是“无论怎么努力都无法知道”。

    关系模型的发明者 e.f. codd 最先给出了这种分类。下图是他对“丢失的信息”的分类

  为什么必须写成“is null”,而不是“= null”

    我相信不少人有这样的困惑吧,尤其是相信刚学 sql 的小伙伴。我们来看个具体的案例,假设我们有如下表以及数据

drop table if exists t_sample_null;
create table t_sample_null (
    id int(11) unsigned not null auto_increment comment '自增主键',
    name varchar(50) not null comment '名称',
    remark varchar(500) comment '备注',
    primary key(id)
) comment 'null样例';

insert into t_sample_null(name, remark)
values('zhangsan', '张三'),('李四', null);

    我们要查询备注为 null 的记录(为 null 这种叫法本身是不对的,只是我们日常中已经叫习惯了,具体往下看),怎么查,很多新手会写出这样的 sql

-- sql 不报错,但查不出结果
select * from t_sample_null where remark = null;

    执行时不报错,但是查不出我们想要的结果, 这是为什么了 ? 这个问题我们先放着,我们往下看

三值逻辑

  这个三值逻辑不是三目运算,指的是三个逻辑值,有人可能有疑问了,逻辑值不是只有真(true)和假(false)吗,哪来的第三个? 说这话时我们需要注意所处的环境,在主流的编程语言中(c、java、python、js等)中,逻辑值确实只有 2 个,但在 sql 中却存在第三个逻辑值:unknown。这有点类似于我们平时所说的:对、错、不知道。

  逻辑值 unknown 和作为 null 的一种的 unknown (未知)是不同的东西。前者是明确的布尔型的逻辑值,后者既不是值也不是变量。为了便于区分,前者采用小写字母 unknown ,后者用大写字母 unknown 来表示。为了让大家理解两者的不同,我们来看一个 x=x 这样的简单等式。x 是逻辑值 unknown 时,x=x 被判断为 true ,而 x 是 unknown 时被判断为 unknown 

-- 这个是明确的逻辑值的比较
unknown = unknown → true

-- 这个相当于null = null
unknown = unknown → unknown

   三值逻辑的逻辑值表

    not

    and

    or

    图中蓝色部分是三值逻辑中独有的运算,这在二值逻辑中是没有的。其余的 sql 谓词全部都能由这三个逻辑运算组合而来。从这个意义上讲,这个几个逻辑表可以说是 sql 的母体(matrix)。
    not 的话,因为逻辑值表比较简单,所以很好记;但是对于 and 和 or,因为组合出来的逻辑值较多,所以全部记住非常困难。为了便于记忆,请注意这三个逻辑值之间有下面这样的优先级顺序。
      and 的情况: false > unknown > true
      or 的情况: true > unknown > false

    优先级高的逻辑值会决定计算结果。例如 true and unknown ,因为 unknown 的优先级更高,所以结果是 unknown 。而 true or unknown 的话,因为 true 优先级更高,所以结果是 true 。记住这个顺序后就能更方便地进行三值逻辑运算了。特别需要记住的是,当 and 运算中包含 unknown 时,结果肯定不会是 true (反之,如果and 运算结果为 true ,则参与运算的双方必须都为 true )。

-- 假设 a = 2, b = 5, c = null,下列表达式的逻辑值如下

a < b and b > c  → unknown
a > b or b < c   → unknown
a < b or b < c   → true
not (b <> c)     → unknown

  “is null” 而非 “= null”

    我们再回到问题:为什么必须写成“is null”,而不是“= null”

    对 null 使用比较谓词后得到的结果总是 unknown 。而查询结果只会包含 where 子句里的判断结果为 true 的行,不会包含判断结果为 false 和 unknown 的行。不只是等号,对 null 使用其他比较谓词,结果也都是一样的。所以无论 remark 是不是 null ,比较结果都是 unknown ,那么永远没有结果返回。以下的式子都会被判为 unknown

-- 以下的式子都会被判为 unknown
= null
> null
< null
<> null
null = null

    那么,为什么对 null 使用比较谓词后得到的结果永远不可能为真呢?这是因为,null 既不是值也不是变量。null 只是一个表示“没有值”的标记,而比较谓词只适用于值。因此,对并非值的 null 使用比较谓词本来就是没有意义的。“列的值为 null ”、“null 值” 这样的说法本身就是错误的。因为 null不是值,所以不在定义域(domain)中。相反,如果有人认为 null 是值,那么我们可以倒过来想一下:它是什么类型的值?关系数据库中存在的值必然属于某种类型,比如字符型或数值型等。所以,假如 null 是值,那么它就必须属于某种类型。

    null 容易被认为是值的原因有两个。第一个是高级编程语言里面,null 被定义为了一个常量(很多语言将其定义为了整数0),这导致了我们的混淆。但是,sql 里的 null 和其他编程语言里的 null 是完全不同的东西。第二个原因是,is null 这样的谓词是由两个单词构成的,所以我们容易把 is 当作谓词,而把 null 当作值。特别是 sql 里还有 is true 、is false 这样的谓词,我们由此类推,从而这样认为也不是没有道理。但是正如讲解标准 sql 的书里提醒人们注意的那样,我们应该把 is null 看作是一个谓词。因此,写成 is_null 这样也许更合适。

温柔的陷阱

  比较谓词和 null

    排中律不成立

      排中律指同一个思维过程中,两个相互矛盾的思想不能同假,必有一真,即“要么a要么非a”

      假设我们有学生表:t_student

drop table if exists t_student;
create table t_student (
    id int(11) unsigned not null auto_increment comment '自增主键',
    name varchar(50) not null comment '名称',
    age int(3) comment '年龄',
    remark varchar(500) not null default '' comment '备注',
    primary key(id)
) comment '学生信息';

insert into t_student(name, age)
value('zhangsan', 25),('wangwu', 60),('bruce', 32),('yzb', null),('boss', 18);

select * from t_student;

      表中数据 yzb 的 age 是 null,也就是说 yzb 的年龄未知。在现实世界里,yzb 是 20 岁,或者不是 20 岁,二者必居其一,这毫无疑问是一个真命题。那么在 sql 的世界里了,排中律还适用吗? 我们来看一个 sql 

select * from t_student
where age = 20 or age <> 20;

      咋一看,这不就是查询表中全部记录吗? 我们来看下实际结果

      yzb 没查出来,这是为什么了?我们来分析下,yzb 的 age 是 null,那么这条记录的判断步骤如下

-- 1. 约翰年龄是 null (未知的 null !)
select *
from t_student
where age = null
or age <> null;

-- 2. 对 null 使用比较谓词后,结果为unknown
select *
from t_student
where unknown
or unknown;

-- 3.unknown or unknown 的结果是unknown (参考三值逻辑的逻辑值表)
select *
from t_student
where unknown;

      sql 语句的查询结果里只有判断结果为 true 的行。要想让 yzb 出现在结果里,需要添加下面这样的 “第 3 个条件”

-- 添加 3 个条件:年龄是20 岁,或者不是20 岁,或者年龄未知
select * from t_student
where age = 20 
    or age <> 20
    or age is null;

    case 表达式和 null

      简单 case 表达式如下

case col_1
    when = 1 then 'o'
    when null then 'x'
end

      这个 case 表达式一定不会返回 ×。这是因为,第二个 when 子句是 col_1 = null 的缩写形式。正如我们所知,这个式子的逻辑值永远是 unknown ,而且 case 表达式的判断方法与 where 子句一样,只认可逻辑值为 true 的条件。正确的写法是像下面这样使用搜索 case 表达式

case when col_1 = 1 then 'o'
    when col_1 is null then 'x'
end

  not in 和 not exists 不是等价的

    我们在对 sql 语句进行性能优化时,经常用到的一个技巧是将 in 改写成 exists ,这是等价改写,并没有什么问题。但是,将 not in 改写成 not exists 时,结果未必一样。

    我们来看个例子,我们有如下两张表:t_student_a 和 t_student_b,分别表示 a 班学生与 b 班学生 

drop table if exists t_student_a;
create table t_student_a (
    id int(11) unsigned not null auto_increment comment '自增主键',
    name varchar(50) not null comment '名称',
    age int(3) comment '年龄',
    city varchar(50) not null comment '城市',
    remark varchar(500) not null default '' comment '备注',
    primary key(id)
) comment '学生信息';

insert into t_student_a(name, age, city)
value
('zhangsan', 25,'深圳市'),('wangwu', 60, '广州市'),
('bruce', 32, '北京市'),('yzb', null, '深圳市'),
('boss', 43, '深圳市');

drop table if exists t_student_b;
create table t_student_b (
    id int(11) unsigned not null auto_increment comment '自增主键',
    name varchar(50) not null comment '名称',
    age int(3) comment '年龄',
    city varchar(50) not null comment '城市',
    remark varchar(500) not null default '' comment '备注',
    primary key(id)
) comment '学生信息';

insert into t_student_b(name, age, city)
value
('马化腾', 45, '深圳市'),('马三', 25, '深圳市'),
('马云', 43, '杭州市'),('李彦宏', 41, '深圳市'),
('年轻人', 25, '深圳市');

select * from t_student_a;
select * from t_student_b;

    需求:查询与 a  班住在深圳的学生年龄不同的 b 班学生,也就说查询出 :马化腾 和 李彦宏,这个 sql 该如何写,像这样?

-- 查询与 a  班住在深圳的学生年龄不同的 b 班学生 ?
select * from t_student_b
where age not in (
    select age from t_student_a 
    where city = '深圳市'
);

    我们来看下执行结果

    我们发现结果是空,查询不到任何数据,这是为什么了 ?这里 null 又开始作怪了,我们一步一步来看看究竟发生了什么

-- 1. 执行子查询,获取年龄列表
select * from t_student
where age not in(43, null, 25);

-- 2. 用not 和in 等价改写not in
select * from t_student
where not age in (43, null, 25);

-- 3. 用or 等价改写谓词in
select * from t_student
where not ( (age = 43) or (age = null) or (age = 25) );

-- 4. 使用德· 摩根定律等价改写
select * from t_student
where not (age = 43) and not(age = null) and not (age = 25);

-- 5. 用<> 等价改写 not 和 =
select * from t_student
where (age <> 43) and (age <> null) and (age <> 25);

-- 6. 对null 使用<> 后,结果为 unknown
select * from t_student
where (age <> 43) and unknown and (age <> 25);

-- 7.如果 and 运算里包含 unknown,则结果不为true(参考三值逻辑的逻辑值表)
select * from t_student
where false 或 unknown;

    可以看出,在进行了一系列的转换后,没有一条记录在 where 子句里被判断为 true 。也就是说,如果 not in 子查询中用到的表里被选择的列中存在 null ,则 sql 语句整体的查询结果永远是空。这是很可怕的现象!

    为了得到正确的结果,我们需要使用 exists 谓词

-- 正确的sql 语句:马化腾和李彦宏将被查询到
select * from t_student_b b
where not exists ( 
    select * from t_student_a a
    where b.age = a.age
    and a.city = '深圳市' 
);

    执行结果如下

    同样地,我们再来一步一步地看看这段 sql 是如何处理年龄为 null 的行的

-- 1. 在子查询里和 null 进行比较运算,此时 a.age 是 null
select * from t_student_b b
where not exists ( 
    select * from t_student_a a
    where b.age = null
    and a.city = '深圳市' 
);

-- 2. 对null 使用“=”后,结果为 unknown
select * from t_student_b b
where not exists ( 
    select * from t_student_a a
    where unknown
    and a.city = '深圳市' 
);

-- 3. 如果and 运算里包含 unknown,结果不会是true
select * from t_student_b b
where not exists ( 
    select * from t_student_a a
    where false 或 unknown
);

-- 4. 子查询没有返回结果,因此相反地,not exists 为 true
select * from t_student_b b
where true;

    也就是说,yzb 被作为 “与任何人的年龄都不同的人” 来处理了。exists 只会返回 true 或者false,永远不会返回 unknown。因此就有了 in 和 exists 可以互相替换使用,而 not in和 not exists 却不可以互相替换的混乱现象。

  还有一些其他的陷阱,比如:限定谓词和 null、限定谓词和极值函数不是等价的、聚合函数和 null 等等。

总结

  1、null 用于表示缺失的值或遗漏的未知数据,不是某种具体类型的值,不能对其使用谓词

  2、对 null 使用谓词后的结果是 unknown,unknown 参与到逻辑运算时,sql 的运行会和预想的不一样

  3、 is null 整个是一个谓词,而不是:is 是谓词,null 是值;类似的还有 is true、is false

  4、要想解决 null 带来的各种问题,最佳方法应该是往表里添加 not null 约束来尽力排除 null

    我的项目中有个硬性规定:所有字段必须是 not null,建表的时候就加上此约束

参考

  《sql进阶教程》

您希望与广大热心网友互动!!点此进行留言回复

相关推荐

神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL !

08-13

在Linux搭建一个邮件服务器的步骤教程

04-11

MySQL 5.7.18 release版安装指南(含有bin文件版本)

06-08

mysql获得60天前unix时间思路及代码

06-05

linux mysql 数据库开启外部访问设置指南

06-05

mysql 开发进阶篇系列 40 mysql日志之二进制日志下以及查询日志

09-12

MySQL 常见错误分析与解决方法

06-09

mysql数据库分表分库的策略

06-08

无法启动mysql服务”1067 进程意外终止”解决办法【简记】

10-11

SQL命令

08-23

最近更新

深入解析Mysql中事务的四大隔离级别及其所解决的读现象

08-18

navicat for mysql 12 的破解安装和基本操作

08-18

MySQL安装与连接

08-18

云上RDS架构

08-18

MySQL管理——操作和查看数据库的命令

08-18

MYSQL入门操作和常规DML、DDL、DQL使用

08-18

入门MySQL——DML语句篇

08-18

入门MySQL——查询语法练习

08-18

MySQL高可用架构:mysql+keepalived实现

08-17

MySQL-下载-安装-配置-多版本共存-设置密码-破解密码

08-17

网友评论

已有0条评论