橘子味的心

SQL ISNULL() 、NVL() 、IFNULL() 和 COALESCE() 函数用于处理表中记录的 NULL 值

这些函数都类似，都实现类似的功能，但并不是每个数据库系统中实现了

下表列出了主流数据库系统中实现的函数

数据库系统	实现的函数
MySQL/MariaDB	IFNULL() 、 COALESCE()
Oracle	NVL()
SQL Server / MS Access	ISNULL()

因为都类似，我们就直接拿 ISNULL() 函数来举例吧

ISNULL 函数判断给定的值或字段是否是 NULL，如果是 NULL 则返回传递的默认值，否则返回给定的值或者字段的值

SELECT IFNULL( column_name, default) FROM table_name;

范例

这些函数都可以独立于表或者数据库而使用，比如下面的语句

SELECT IFNULL('www.twle.cn','twle.cn'), IFNULL(NULL,'twle.cn');

运行结果如下

mysql> SELECT IFNULL('www.twle.cn','twle.cn'), IFNULL(NULL,'twle.cn');
+---------------------------------+------------------------+
| IFNULL('www.twle.cn','twle.cn') | IFNULL(NULL,'twle.cn') |
+---------------------------------+------------------------+
| www.twle.cn                     | twle.cn                |
+---------------------------------+------------------------+

演示数据

先在 MySQL 数据库运行下面的语句创建测试数据

CREATE DATABASE IF NOT EXISTS twle default character set utf8mb4 collate utf8mb4_unicode_ci;

USE twle;

DROP TABLE IF EXISTS `lession_views`;

CREATE TABLE `lession_views` (
    uniq bigint(20) primary key NOT NULL default '0' ,
    lession_name varchar(32) default '',
    lession_id int(11) default '0',
    date_at  int(11) NOT NULL default '0',
    views int(11) default '0'
);

INSERT INTO lession_views(uniq,lession_name,lession_id,date_at,views) VALUES
(20170511000001,'Python 基础教程',1,20170511,320),
(20170511000002,'Scala 基础教程', 2,20170511,22),
(20170511000003,'Ruby 基础教程', 3, 20170511,49),
(20170512000001,'Python 基础教程',1,20170512,220),
(20170512000002,'Scala 基础教程',2,20170512,12),
(20170512000003,'Ruby 基础教程',3,20170512,63),
(20170513000001,'Python 基础教程',1,20170513,441),
(20170513000002,'Scala 基础教程',2,20170513,39),
(20170513000003,'Ruby 基础教程',3,20170513,NULL);

使用 SELECT * FROM lession_views; 运行结果如下

+----------------+---------------------+------------+----------+-------+
| uniq           | lession_name        | lession_id | date_at  | views |
+----------------+---------------------+------------+----------+-------+
| 20170511000001 | Python 基础教程     |          1 | 20170511 |   320 |
| 20170511000002 | Scala 基础教程      |          2 | 20170511 |    22 |
| 20170511000003 | Ruby 基础教程       |          3 | 20170511 |    49 |
| 20170512000001 | Python 基础教程     |          1 | 20170512 |   220 |
| 20170512000002 | Scala 基础教程      |          2 | 20170512 |    12 |
| 20170512000003 | Ruby 基础教程       |          3 | 20170512 |    63 |
| 20170513000001 | Python 基础教程     |          1 | 20170513 |   441 |
| 20170513000002 | Scala 基础教程      |          2 | 20170513 |    39 |
| 20170513000003 | Ruby 基础教程       |          3 | 20170513 |  NULL |
+----------------+---------------------+------------+----------+-------+

总共有 10 条记录，最后一条记录的 views 值为 NULL

IFNULL() 函数的作用

SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数有啥作用呢？

注意: 测试数据的最后一条记录的 views 值为 NULL

假设我们现在要给每个访问量加上 1000，那么可以使用下面的语句

SELECT lession_name,views + 1000  FROM lession_views;

运行结果如下

mysql> SELECT lession_name,views + 1000  FROM lession_views;
+---------------------+--------------+
| lession_name        | views + 1000 |
+---------------------+--------------+
| Python 基础教程     |         1320 |
| Scala 基础教程      |         1022 |
| Ruby 基础教程       |         1049 |
| Python 基础教程     |         1220 |
| Scala 基础教程      |         1012 |
| Ruby 基础教程       |         1063 |
| Python 基础教程     |         1441 |
| Scala 基础教程      |         1039 |
| Ruby 基础教程       |         NULL |
+---------------------+--------------+

等等，为啥最后一条记录的值是 NULL 而不是 1000 ？

在这里，我们希望 NULL 值为 0

这是因为，NULL 值只能用于 IS NULL 或者 IS NOT NULL 计算，而不能用于普通的加减乘除运算

如果我们必须要这么做，那么可以使用 ISNULL() 等函数转换为其它值，比如 0

所以，我们将 SQL 语句改改，改成下面这样

SELECT lession_name,IFNULL( views,0) + 1000  FROM lession_views;

运行结果如下

mysql> SELECT lession_name,IFNULL( views,0) + 1000  FROM lession_views;
+---------------------+-------------------------+
| lession_name        | IFNULL( views,0) + 1000 |
+---------------------+-------------------------+
| Python 基础教程     |                    1320 |
| Scala 基础教程      |                    1022 |
| Ruby 基础教程       |                    1049 |
| Python 基础教程     |                    1220 |
| Scala 基础教程      |                    1012 |
| Ruby 基础教程       |                    1063 |
| Python 基础教程     |                    1441 |
| Scala 基础教程      |                    1039 |
| Ruby 基础教程       |                    1000 |
+---------------------+-------------------------+

终于是正确了

注意

聚合函数会自动忽略 NULL 的值

比如我们要统计每门课程的访问量，可以使用下面的 SQL 语句

SELECT lession_name, SUM(views) FROM lession_views GROUP BY lession_name;

运行结果如下

mysql> SELECT lession_name, SUM(views) FROM lession_views GROUP BY lession_name; 
+---------------------+------------+
| lession_name        | SUM(views) |
+---------------------+------------+
| Python 基础教程      |        981 |
| Ruby 基础教程        |        112 |
| Scala 基础教程       |         73 |
+---------------------+------------+

结果神奇的很正确了

标题：SQL NULL 函数

范例

演示数据

IFNULL() 函数的作用

注意

目录

分类