ITPub博客

首页 > 数据库 > MySQL > 【MySQL编程你也行】MySQL 中的NULL函数:isnull、ifnull、nullif、coalesce

【MySQL编程你也行】MySQL 中的NULL函数:isnull、ifnull、nullif、coalesce

原创 MySQL 作者:gulunhua 时间:2015-10-23 16:53:33 0 删除 编辑

1、isnull(xx):是否是null,类似于is null

点击(此处)折叠或打开

  1. mysql> set @v = 'a';
  2. Query OK, 0 rows affected (0.03 sec)

  3. mysql> select isnull(@v);
  4. +------------+
  5. | isnull(@v) |
  6. +------------+
  7. | 0 |
  8. +------------+
  9. 1 row in set (0.01 sec)

  10. mysql> set @v = null;
  11. Query OK, 0 rows affected (0.01 sec)

  12. mysql> select isnull(@v);
  13. +------------+
  14. | isnull(@v) |
  15. +------------+
  16. | 1 |
  17. +------------+
  18. 1 row in set (0.01 sec)

2、ifnull(xx,yy):如果xx是null,那么返回指定yy,否则还是返回xx。

点击(此处)折叠或打开

  1. mysql> set @v = 'a';
  2. Query OK, 0 rows affected (0.01 sec)

  3. mysql> select ifnull(@v,'wc');
  4. +-----------------+
  5. | ifnull(@v,'wc') |
  6. +-----------------+
  7. | a |
  8. +-----------------+
  9. 1 row in set (0.00 sec)

  10. mysql> set @v = null;
  11. Query OK, 0 rows affected (0.02 sec)

  12. mysql> select ifnull(@v,'wc');
  13. +-----------------+
  14. | ifnull(@v,'wc') |
  15. +-----------------+
  16. | wc |
  17. +-----------------+
  18. 1 row in set (0.01 sec)

3、nullif(xx,yy) :如果xx=yy,则返回null,否则还是返回xx

点击(此处)折叠或打开

  1. mysql> set @v = 'a';
  2. Query OK, 0 rows affected (0.01 sec)

  3. mysql> select nullif(@v,'wc');
  4. +-----------------+
  5. | nullif(@v,'wc') |
  6. +-----------------+
  7. | a |
  8. +-----------------+
  9. 1 row in set (0.04 sec)

  10. mysql> select nullif(@v,'a');
  11. +----------------+
  12. | nullif(@v,'a') |
  13. +----------------+
  14. | NULL |
  15. +----------------+
  16. 1 row in set, 1 warning (0.02 sec)

4、coalesce(xx,yy,zz...):返回一个列表里第一个非null的值

点击(此处)折叠或打开

  1. mysql> set @v = 'a';
  2. Query OK, 0 rows affected (0.01 sec)

  3. mysql> select coalesce(null,@v,null,3);
  4. +--------------------------+
  5. | coalesce(null,@v,null,3) |
  6. +--------------------------+
  7. | a |
  8. +--------------------------+
  9. 1 row in set (0.02 sec)

  10. mysql> set @v = null;
  11. Query OK, 0 rows affected (0.01 sec)

  12. mysql> select coalesce(null,@v,null,3);
  13. +--------------------------+
  14. | coalesce(null,@v,null,3) |
  15. +--------------------------+
  16. | 3 |
  17. +--------------------------+
  18. 1 row in set (0.01 sec)


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/653220/viewspace-1815925/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-01

  • 博文量
    41
  • 访问量
    30171