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

  • 时间:
  • 浏览:0
  • 来源:大发pk10_pk10信誉网_大发pk10信誉网

前言

  开心一刻 

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

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