神奇的 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(200) NOT NULL COMMENT '名称',
    remark VARCHAR(2000) 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(200) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    remark VARCHAR(2000) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

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

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