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

  • 时间:
  • 浏览:3
  • 来源:1分时时彩官网_1分时时彩投注平台注册_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(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 谓词详细都能由你你这个个逻辑运算组合而来。从你你这个意义上讲,你你这个几次逻辑表还时需说是 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 是详细不同的东西。第5个原因分析分析是,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 表达式一定回会返回 ×。这是很久,第5个 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