MySQL数据库NULL值的处理

数据库 MySQL
以下的文章主要介绍的是MySQL数据库NULL的实际用法以及其实际应用代码的描述,以下就是文章的详细内容描述,望你会有所收获。

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

1. 对含空值列进行排序

建表:

  1. mysql> create table t1(col1 int primary key, col2 varchar(2),col3 int);   
  2. Query OK, 0 rows affected (0.24 sec)  

加入数据:

 

  1. 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);   
  2. Query OK, 10 rows affected (0.08 sec)   
  3. Records: 10 Duplicates: 0 Warnings: 0  

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

 

  1. mysql> select * FROM t1 order by 3;   
  2. +------+------+------+   
  3. | col1 | col2 | col3 |   
  4. +------+------+------+   
  5. | 6 | F | NULL |   
  6. | 2 | B | NULL |   
  7. | 3 | C | NULL |   
  8. | 9 | I | NULL |   
  9. | 1 | A | 10 |   
  10. | 7 | G | 20 |   
  11. | 5 | E | 30 |   
  12. | 4 | D | 50 |   
  13. | 10 | J | 60 |   
  14. | 8 | H | 90 |   
  15. +------+------+------+   
  16. 10 rows in set (0.00 sec)   

但是,怎样实现如下效果呢:

  1. +------+------+------+   
  2. | col1 | col2 | col3 |   
  3. +------+------+------+   
  4. | 1 | A | 10 |   
  5. | 7 | G | 20 |   
  6. | 5 | E | 30 |   
  7. | 4 | D | 50 |   
  8. | 10 | J | 60 |   
  9. | 8 | H | 90 |   
  10. | 2 | B | NULL |   
  11. | 3 | C | NULL |   
  12. | 6 | F | NULL |   
  13. | 9 | I | NULL |   
  14. +------+------+------+  
  15. 10 rows in set (0.00 sec)   
  16. SOLUTION:   
  17. mysql> SELECT col1, col2, col3 FROM (   
  18. -> SELECT col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1) as n   
  19. -> ORDER BY n.IS_NULL DESC, col3;  

 

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

 

  1. mysql> select col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1;   
  2. +------+------+------+---------+ |  
  3. | col1 | col2 | col3 | IS_NULL |   
  4. +------+------+------+---------+   
  5. | 1 | A | 10 | 1 |   
  6. | 2 | B | NULL | 0 |   
  7. | 3 | C | NULL | 0 |   
  8. | 4 | D | 50 | 1 |   
  9. | 5 | E | 30 | 1 |   
  10. | 6 | F | NULL | 0 |  
  11. | 7 | G | 20 | 1 |   
  12. | 8 | H | 90 | 1 |   
  13. | 9 | I | NULL | 0 |   
  14. | 10 | J | 60 | 1 |   
  15. +------+------+------+---------+   
  16. 10 rows in set (0.00 sec)   

 

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

2. 运用 ORDER BY NULL 禁止排序

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

  1. R,'` A.Kk   
  2. 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

  1. id: 1   
  2. select_type: SIMPLE   
  3. table: t1   
  4. type: ALL    
  5. possible_keys: NULL   
  6. key: NULL   
  7. key_len: NULL   
  8. ref: NULL   
  9. rows: 10   
  10. Extra: Using temporary; Using filesort   
  11. 1 row in set (0.00 sec)   
  12. 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

  1. id: 1   
  2. select_type: SIMPLE   
  3. table: t1   
  4. type: ALL   
  5. possible_keys: NULL   
  6. key: NULL   
  7. key_len: NULL ts   
  8. ref: NULL   
  9. rows: 10   
  10. Extra: Using temporary   
  11. 1 row in set (0.00 sec)  

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

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

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

  1. mysql> CREATE TABLE t2 (col1 int default NULL, col2 int default NULL);   
  2. Query OK, 0 rows affected (0.01 sec)   
  3. mysql> CREATE TABLE t3 (col1 int default NULL, col2 int default NULL);   
  4. Query OK, 0 rows affected (0.01 sec)  

加入数据:

  1. mysql> INSERT INTO t2 VALUES (1,2),(1,3);   
  2. Query OK, 2 rows affected (0.00 sec)   
  3. Records: 2 Duplicates: 0 Warnings: 0   
  4. mysql> INSERT INTO t3 VALUES (1,2),(1,NULL);   
  5. Query OK, 2 rows affected (0.00 sec)   
  6. Records: 2 Duplicates: 0 Warnings: 0  

执行如下查询:

  1. mysql> SELECT * FROM t2 WHERE col2 NOT IN (SELECT col2 FROM t3);   
  2. Empty set (0.00 sec)   
  3. mysql> SELECT * FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3 WHERE t3.col2 = t2.col2);   
  4. +------+------+   
  5. | col1 | col2 |   
  6. +------+------+   
  7. | 1 | 3 |   
  8. +------+------+   
  9. 1 row in set (0.00 sec)  

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

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

  1. mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;   
  2. +----------+-----------+----------+----------+   
  3. 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |   
  4. +----------+-----------+----------+----------+   
  5. | NULL | NULL | NULL | NULL |   
  6. +----------+-----------+----------+----------+   
  7. 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 的介绍,望你能有所收获。

 

【编辑推荐】

  1. MySQL mysqldump命令的正确应用
  2. 使用MySQL 数据库出现的困难解决
  3. jsp MySQL 中的中文问题的产生与解决
  4. MySQL命令导数据的实际操作与代码
  5. 用c语言正确读取MySQL数据库实战演示
责任编辑:佚名 来源: 博客园
相关推荐

2011-04-07 15:47:28

MySQL数据库

2021-01-26 13:40:44

mysql数据库

2017-10-23 16:06:41

数据库MySQL复制中断

2011-07-11 14:36:10

BinlogMysql

2011-07-12 16:41:14

mysql处理异常

2011-08-05 14:02:17

MySQL数据库异常处理

2010-05-05 14:13:52

Oracle数据

2010-06-04 14:59:06

MySQL数据库

2011-03-08 08:49:55

MySQL优化单机

2023-10-26 14:30:05

MySQLInnoDB

2011-05-26 14:43:49

ORACLE数据库异常处理

2011-05-13 09:42:21

2011-02-22 14:26:04

ProFTPD

2011-02-22 14:26:04

ProFTPD

2009-05-08 09:56:37

MaxDBMySQL数据库管理

2011-08-23 18:46:27

MySQLTIMESTAMP

2010-05-14 17:34:36

MySQL数据库列值

2019-07-05 07:50:52

数据库空值查询

2011-08-23 18:30:59

MySQLTIMESTAMP

2011-03-09 08:53:02

MySQL优化集群
点赞
收藏

51CTO技术栈公众号