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

  • 时间:
  • 浏览:0
  • 来源:大发uu快3_uu快3官方版app下载_大发uu快3官方版app下载

前言

  开心一刻 

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

    主持人问:“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(100) NOT NULL COMMENT '名称',
    remark VARCHAR(100) 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(100) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    remark VARCHAR(100) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

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

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