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

  • 时间:
  • 浏览:8
  • 来源:幸运快3_快3玩法_幸运快3玩法

前言

  开心一刻 

     另另2个中国小孩参加国外的脱口秀节目,回会 语言不通,于是找了另另2个翻译。

    主持人问:“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;

    执行时不报错,假如查不出亲戚亲戚让当当我们 我想要的结果, 这是为那此了 ? 你你是什么 问提亲戚亲戚让当当我们 先放着,亲戚亲戚让当当我们 往下看

三值逻辑

  你你是什么 三值逻辑回会三目运算,指的是另另2个逻辑值,许多人回会 有问提了,逻辑值回会只能真(true)和假(false)吗,哪来的第另另2个? 说这话时亲戚亲戚让当当我们 须要注意发生的环境,在主流的编程语言中(C、JAVA、Python、JS等)中,逻辑值确实只能 2 个,但在 SQL 中却发生第另另2个逻辑值:unknown。这特别之类于亲戚亲戚让当当我们 平时所说的:对、错、我想知道。

  逻辑值 unknown 和作为 NULL 的并是否是 的 UNKNOWN (未知)是不同的东西。前者是明确的布尔型的逻辑值,后者既回会值也回会变量。为了便于区分,前者采用小写字母 unknown ,后者用大写字母 UNKNOWN 来表示。为了让亲戚亲戚让当当我们 理解两者的不同,亲戚亲戚让当当我们 来看另另2个 x=x 原先的简单等式。x 是逻辑值 unknown 时,x=x 被判断为 true ,而 x 是 UNKNOWN 时被判断为 unknown 

-- 你你是什么

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

-- 你你是什么

共要NULL = NULL
UNKNOWN = UNKNOWN → unknown

   三值逻辑的逻辑值表

    NOT

    AND

    OR

    图中天蓝色主次是三值逻辑中独有的运算,这在二值逻辑中是这麼的。其余的 SQL 谓词完全都能由这另另2个逻辑运算组合而来。从你你是什么 意义上讲,你你是什么 2个逻辑表都须要说是 SQL 的母体(matrix)。

    NOT 的话,回会 逻辑值表比较简单,好多好多 很好记;假如对于 AND 和 OR,回会 组合出来的逻辑值较多,好多好多 完全记住非常困难。为了便于记忆,请注意这另另2个逻辑值之间有下面原先的优先级顺序。

      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 好多好多 另另2个表示“这麼值”的标记,而比较谓词只适用于值。假如,对太久值的 NULL 使用比较谓词原先好多好多 这麼意义的。“列的值为 NULL ”、“NULL 值” 原先的说法并是否是 好多好多 错误的。回会 NULL回会值,好多好多 不出定义域(domain)中。相反,回会 许多人认为 NULL 是值,这麼亲戚亲戚让当当我们 都须要倒过来想一下:它是那此类型的值?关系数据库中发生的值必然属于并是否是 类型,比如字符型或数值型等。好多好多 ,假如 NULL 是值,这麼它就须要属于并是否是 类型。

    NULL 容易被认为是值的是因为另另有2个。第另另2个是高级编程语言上面,NULL 被定义为了另另2个常量(好多好多 语言将其定义为了整数0),这是因为了亲戚亲戚让当当我们 的混淆。假如,SQL 里的 NULL 和某些编程语言里的 NULL 是完全不同的东西。第还还有一个是因为是,IS NULL 原先的谓词是由另另2个单词构成的,好多好多 亲戚亲戚让当当我们 容易把 IS 当作谓词,而把 NULL 当作值。特别是 SQL 里还有 IS TRUE 、IS FALSE 原先的谓词,亲戚亲戚让当当我们 由此类推,从而原先认为也回会这麼道理。假如正如讲解标准 SQL 的书里提醒亲戚亲戚让当当我们 注意的那样,亲戚亲戚让当当我们 应该把 IS NULL 看作是另另2个谓词。假如,写成 IS_NULL 原先也许更共要。

温柔的陷阱

  比较谓词和 NULL

    排中律不成立

      排中律指同另另2个思维过程中,另另2个相互矛盾的思想只能同假,必有一真,即“要么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 岁,二者必居其一,这毫无问提是另另2个真命题。这麼在 SQL 的世界里了,排中律还适用吗? 亲戚亲戚让当当我们 来看另另2个 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 的话进行性能优化时,突然用到的另另2个技巧是将 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 整个是另另2个谓词,而回会:IS 是谓词,NULL 是值;之类的还有 IS TRUE、IS FALSE

  4、要想处理 NULL 带来的各种问提,最佳最好的妙招应该是往表里打上去 NOT NULL 约束来尽力排除 NULL

    我的项目富含个硬性规定:所有字段须就是 NOT NULL,建表的回会 就打上去此约束

参考

  《SQL进阶教程》