mysql数据库中的null值_MySQL数据库NULL值的处理

我们大家都知道MySQL数据库NULL其有非常独特的逻辑意义，对于NULL值的处理问题可以说是令人一大头疼的事情，所以今天我特意整理了一些比较实用的TIPS，希望会给你带来一些帮助在此方面，疏漏之处敬请批评 。

1. 对含空值列进行排序

建表：

mysql>create table t1(col1 int primary key, col2 varchar(2),col3 int);

Query OK, 0 rows affected (0.24 sec)

加入数据：

mysql>insert into t1 values (1,'A',10),(2,'B',NULL),(3,'C',NULL),(4,'D',50),(5,'E',30),(6,'F',NULL),(7,'G',20),(8,'H',90),(9,'I',NULL),(10,'J',60);

Query OK, 10 rows affected (0.08 sec)

Records: 10 Duplicates: 0 Warnings: 0

我们知道MySQL在排序过程中总是将NULL当作“最小值”处理。例如：

mysql>select * FROM t1 order by 3;

+------+------+------+

| col1 | col2 | col3 |

+------+------+------+

| 6 | F | NULL |

| 2 | B | NULL |

| 3 | C | NULL |

| 9 | I | NULL |

| 1 | A | 10 |

| 7 | G | 20 |

| 5 | E | 30 |

| 4 | D | 50 |

| 10 | J | 60 |

| 8 | H | 90 |

+------+------+------+

10 rows in set (0.00 sec)

但是，怎样实现如下效果呢：

+------+------+------+

| col1 | col2 | col3 |

+------+------+------+

| 1 | A | 10 |

| 7 | G | 20 |

| 5 | E | 30 |

| 4 | D | 50 |

| 10 | J | 60 |

| 8 | H | 90 |

| 2 | B | NULL |

| 3 | C | NULL |

| 6 | F | NULL |

| 9 | I | NULL |

+------+------+------+

10 rows in set (0.00 sec)

SOLUTION：

mysql>SELECT col1, col2, col3 FROM (

->SELECT col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1) as n

->ORDER BY n.IS_NULL DESC, col3;

运用 'CASE'表达式构造一个附加列，并为空值和非空值分别赋值，然后以该附加列作为排序条件。

mysql>select col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1;

+------+------+------+---------+ |

| col1 | col2 | col3 | IS_NULL |

+------+------+------+---------+

| 1 | A | 10 | 1 |

| 2 | B | NULL | 0 |

| 3 | C | NULL | 0 |

| 4 | D | 50 | 1 |

| 5 | E | 30 | 1 |

| 6 | F | NULL | 0 |

| 7 | G | 20 | 1 |

| 8 | H | 90 | 1 |

| 9 | I | NULL | 0 |

| 10 | J | 60 | 1 |

+------+------+------+---------+

10 rows in set (0.00 sec)

通过构造附加列对含MySQL数据库NULL列排序，可以轻松决定含空值的记录在结果集中的位置。

2. 运用 ORDER BY NULL 禁止排序

若查询中包含 GROUP BY　但我们希望避免对结果集排序从而减少消耗，可以运用　ORDER BY NULL 禁止排序。

R,'` A.Kk

mysql>EXPLAIN SELECT SUM(col3),CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1 GROUP BY IS_NULL\G;

1. row

id: 1

select_type: SIMPLE

table: t1

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using temporary; Using filesort

1 row in set (0.00 sec)

mysql>EXPLAIN SELECT SUM(col3),CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1 GROUP BY IS_NULL m)ORDER BY NULL\G;

1. row

id: 1

select_type: SIMPLE

table: t1

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL ts

ref: NULL

rows: 10

Extra: Using temporary

1 row in set (0.00 sec)

可见运用了ORDER BY NULL后查询便没有进行filesort，在较大结果集中filesort操作往往相当耗时。

3. 子查询 NOT IN 与 NOT EXISTS　中的NULL

有些情况下　NOT IN　形式的子查询返回空结果集，但是将其改写为 NOT EXISTS　形式后则恢复正常，如下所示：

建表：

mysql>CREATE TABLE t2 (col1 int default NULL, col2 int default NULL);

Query OK, 0 rows affected (0.01 sec)

mysql>CREATE TABLE t3 (col1 int default NULL, col2 int default NULL);

Query OK, 0 rows affected (0.01 sec)

加入数据：

mysql>INSERT INTO t2 VALUES (1,2),(1,3);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql>INSERT INTO t3 VALUES (1,2),(1,NULL);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

执行如下查询：

mysql>SELECT * FROM t2 WHERE col2 NOT IN (SELECT col2 FROM t3);

Empty set (0.00 sec)

mysql>SELECT * FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3 WHEREt3.col2=t2.col2);

+------+------+

| col1 | col2 |

+------+------+

| 1 | 3 |

+------+------+

1 row in set (0.00 sec)

为什么会这样呢？这要从MySQL数据库NULL的特殊性说起：

在MySQL中有三种状态：True、False、Unknown，任何NULL的比较操作都是Unknown状态，如下所示：

mysql>SELECT1=NULL, 1<>NULL, 1NULL;

+----------+-----------+----------+----------+

|1=NULL| 1<>NULL | 1NULL |

+----------+-----------+----------+----------+

| NULL | NULL | NULL | NULL |

+----------+-----------+----------+----------+

1 row in set (0.00 sec)

而且所有的查询条件(ON, WHERE, HAVING)都是将Unknown状态当做False处理。

所以***条查询的查询田间等同于：col2 NOT IN (2, NULL) => col2 <> 2 AND col2 <> NULL => true AND Unknow => Unknow => False

查询条件永为False，故该查询没有返回结果。

而 NOT EXISTS 是循环执行的

他首先执行 SELECT 1 FROM t3 WHERE t3.col2 = 2

返回了结果，经 NOT EXISTS 操作后查询条件为 False，故不做任何输出，

接下来执行 SELECT 1 FROM t3 WHERE t3.col2 = 3

无返回结果。经 NOT EXISTS 操作后查询条件为 True，于是输出本次查询结果。

所以，如果当一个 NOT IN 子查询没有返回结果的时候，应该特别注意内层查询的结果集是否包含空值，若包含的话，应尝试将查询改写为 NOT EXISTS 形式。

这种做法在有些情况下还可导致性能的提升。以上的相关内容就是对MySQL数据库NULL 的介绍，望你能有所收获。

【编辑推荐】

【责任编辑：孙巧华 TEL：(010)68476606】

点赞 0