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

  • 时间:
  • 浏览:0

前言

  开心一刻 

     一个多多多多中国小孩参加国外的脱口秀节目,完后 语言不通,于是找了一个多多多多翻译。

    主持人问:“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(50) 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(50) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student(name, age)
VALUE('zhangsan', 25),('wangwu', 50),('bruce', 32),('yzb', NULL),('boss', 18);

SELECT * FROM t_student;
View Code

      表中数据 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(50) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student_A(name, age, city)
VALUE
('zhangsan', 25,'深圳市'),('wangwu', 50, '广州市'),
('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(50) 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;
View Code

    需求:查询与 A  班住在深圳的学生年龄不同的 B 班学生,也许多许多查询出 :马化腾 和 李彦宏,你你什儿 SQL 该如可写,像原本?

-- 查询与 A  班住在深圳的学生年龄不同的 B 班学生 ?
SELECT * FROM t_student_B
WHERE age NOT IN (
    SELECT age FROM t_student_A 
    WHERE city = '深圳市'
);

    另一个人所有来看下执行结果

    另一个人所有发现结果是空,查询非要任何数据,这是为社 了 ?这里 NULL 又开始英文了了作怪了,另一个人所有一步一步来看看究竟位于了什么

    还还要看出,在进行了一系列的转换后,越来越 第一根记录在 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 的行的

    也许多许多说,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进阶教程》

navicat

  https://gitee.com/youzhibing/tools/blob/master/NavicatforMySQL.rar