mysqlnull所占空间（详解mysql三值逻辑与NULL）

mysqlnull所占空间

详解mysql三值逻辑与NULL

什么是null

null 用于表示缺失的值或遗漏的未知数据，不是某种具体类型的值。数据表中的 null 值表示该值所处的字段为空，值为 null 的字段没有值，尤其要明白的是：null 值与 0 或者空字符串是不同的。

两种 null

这种说法大家可能会觉得很奇怪，因为 sql 里只存在一种 null 。然而在讨论 null 时，我们一般都会将它分成两种类型来思考：“未知”（unknown）和“不适用”（not applicable,inapp licable）。

以“不知道戴墨镜的人眼睛是什么颜色”这种情况为例，这个人的眼睛肯定是有颜色的，但是如果他不摘掉眼镜，别人就不知道他的眼睛是什么颜色。这就叫作未知。而“不知道冰箱的眼睛是什么颜色”则属于“不适用”。因为冰箱根本就没有眼睛，所以“眼睛的颜色”这一属性并不适用于冰箱。“冰箱的眼睛的颜色”这种说法和“圆的体积”“男性的分娩次数”一样，都是没有意义的。平时，我们习惯了说“不知道”，但是“不知道”也分很多种。“不适用”这种情况下的 null ，在语义上更接近于“无意义”，而不是“不确定”。这里总结一下：“未知”指的是“虽然现在不知道，但加上某些条件后就可以知道”；而“不适用”指的是“无论怎么努力都无法知道”。

关系模型的发明者 e.f. codd 最先给出了这种分类。下图是他对“丢失的信息”的分类

为什么必须写成“is null”，而不是“＝ null”

我相信不少人有这样的困惑吧，尤其是相信刚学 sql 的小伙伴。我们来看个具体的案例，假设我们有如下表以及数据

?

1 2 3 4 5 6 7 8 9 10

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(500) comment '备注',     primary key(id) ) comment 'null样例';   insert into t_sample_null(name, remark) values('zhangsan', '张三'),('李四', null);

我们要查询备注为 null 的记录（为 null 这种叫法本身是不对的，只是我们日常中已经叫习惯了，具体往下看），怎么查，很多新手会写出这样的 sql

?

1 2	-- 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

?

1 2 3 4 5

-- 这个是明确的逻辑值的比较 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 ）。

?

1 2 3 4 5 6

-- 假设 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

?

1 2 3 4 5 6

-- 以下的式子都会被判为 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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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(500) not null default '' comment '备注',     primary key(id) ) comment '学生信息';   insert into t_student(name, age) value('zhangsan', 25),('wangwu', 60),('bruce', 32),('yzb', null),('boss', 18);   select * from t_student;

表中数据 yzb 的 age 是 null，也就是说 yzb 的年龄未知。在现实世界里，yzb 是 20 岁，或者不是 20 岁，二者必居其一，这毫无疑问是一个真命题。那么在 sql 的世界里了，排中律还适用吗? 我们来看一个 sql

?

1 2	select * from t_student where age = 20 or age <> 20;

咋一看，这不就是查询表中全部记录吗？我们来看下实际结果

yzb 没查出来，这是为什么了？我们来分析下，yzb 的 age 是 null，那么这条记录的判断步骤如下

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

-- 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 个条件”

?

1 2 3 4 5

-- 添加 3 个条件：年龄是20 岁，或者不是20 岁，或者年龄未知 select * from t_student where age = 20     or age <> 20     or age is null;

case 表达式和 null

简单 case 表达式如下

?

1 2 3 4

case col_1     when = 1 then 'o'     when null then 'x' end

这个 case 表达式一定不会返回 ×。这是因为，第二个 when 子句是 col_1 = null 的缩写形式。正如我们所知，这个式子的逻辑值永远是 unknown ，而且 case 表达式的判断方法与 where 子句一样，只认可逻辑值为 true 的条件。正确的写法是像下面这样使用搜索 case 表达式

?

1 2 3

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 班学生

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

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(500) not null default '' comment '备注',     primary key(id) ) comment '学生信息';   insert into t_student_a(name, age, city) value ('zhangsan', 25,'深圳市'),('wangwu', 60, '广州市'), ('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(500) not null default '' comment '备注',     primary key(id) ) comment '学生信息';   insert into t_student_b(name, age, city) value ('马化腾', 45, '深圳市'),('马三', 25, '深圳市'), ('马云', 43, '杭州市'),('李彦宏', 41, '深圳市'), ('年轻人', 25, '深圳市');    * from t_student_b;

需求：查询与 a 班住在深圳的学生年龄不同的 b 班学生，也就说查询出 ：马化腾 和 李彦宏，这个 sql 该如何写，像这样？

?

1 2 3 4 5 6

-- 查询与 a  班住在深圳的学生年龄不同的 b 班学生 ? select * from t_student_b where age not in (     select age from t_student_a     where city = '深圳市' );

我们来看下执行结果

我们发现结果是空，查询不到任何数据，这是为什么了 ？这里 null 又开始作怪了，我们一步一步来看看究竟发生了什么

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

-- 1. 执行子查询，获取年龄列表 select * from t_student where age not in(43, null, 25);   -- 2. 用not 和in 等价改写not in select * from t_student where not age in (43, null, 25);   -- 3. 用or 等价改写谓词in select * from t_student where not ( (age = 43) or (age = null) or (age = 25) );   -- 4. 使用德· 摩根定律等价改写 select * from t_student where not (age = 43) and not(age = null) and not (age = 25);   -- 5. 用<> 等价改写 not 和 = select * from t_student where (age <> 43) and (age <> null) and (age <> 25);   -- 6. 对null 使用<> 后，结果为 unknown select * from t_student where (age <> 43) and unknown and (age <> 25);   -- 7．如果 and 运算里包含 unknown，则结果不为true（参考三值逻辑的逻辑值表） select * from t_student where false 或 unknown;

可以看出，在进行了一系列的转换后，没有一条记录在 where 子句里被判断为 true 。也就是说，如果 not in 子查询中用到的表里被选择的列中存在 null ，则 sql 语句整体的查询结果永远是空。这是很可怕的现象！

为了得到正确的结果，我们需要使用 exists 谓词

?

1 2 3 4 5 6 7

-- 正确的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 的行的

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

-- 1. 在子查询里和 null 进行比较运算，此时 a.age 是 null select * from t_student_b b where not exists (     select * from t_student_a a     where b.age = null     and a.city = '深圳市' );   -- 2. 对null 使用“=”后，结果为 unknown select * from t_student_b b where not exists (     select * from t_student_a a     where unknown     and a.city = '深圳市' );   -- 3. 如果and 运算里包含 unknown，结果不会是true select * from t_student_b b where not exists (     select * from t_student_a a     where false 或 unknown );   -- 4. 子查询没有返回结果，因此相反地，not exists 为 true select * from t_student_b b where true;

也就是说，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 是值；类似的还