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

  • 时间:
  • 浏览:1
  • 来源:幸运快3_快3大小_幸运快3大小

前言

  开心一刻 

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

    主持人问:“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(80) NOT NULL COMMENT '名称',
    remark VARCHAR(800) 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 谓词完整篇 都能由这另三个小逻辑运算组合而来。从你这俩 意义上讲,你这俩 2个逻辑表上能 说是 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(80) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    remark VARCHAR(800) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

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

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