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

  • 时间:
  • 浏览:1

前言

  开心一刻 

     另另四个多中国小孩参加国外的脱口秀节目,意味着语言不通,于是找了另另四个多翻译。

    主持人问:“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(3000) NOT NULL COMMENT '名称',
    remark VARCHAR(30000) 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(3000) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    remark VARCHAR(30000) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student(name, age)
VALUE('zhangsan', 25),('wangwu', 300),('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(3000) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    city VARCHAR(3000) NOT NULL COMMENT '城市',
    remark VARCHAR(30000) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student_A(name, age, city)
VALUE
('zhangsan', 25,'深圳市'),('wangwu', 300, '广州市'),
('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(3000) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    city VARCHAR(3000) NOT NULL COMMENT '城市',
    remark VARCHAR(30000) 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