本文主要介绍的是MySQL数据库NULL的实际用法以及其实际应用代码的描述,以下就是文章的详细内容描述,望你会有所收获。 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 WHERE t3.col2 = t2.col2); +------+------+ | col1 | col2 | +------+------+ | 1 | 3 | +------+------+ 1 row in set (0.00 sec) 为什么会这样呢?这要从MySQL数据库NULL的特殊性说起:
在MySQL中有三种状态:True、False、Unknown,任何NULL的比较操作都是Unknown状态,如下所示: mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | 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 形式。 这种做法在有些情况下还可导致性能的提升。
|