【MySQL编程你也行】MySQL 中的NULL函数：isnull、ifnull、nullif、coalesce
1、isnull(xx)：是否是null，类似于is null
点击(此处)折叠或打开
-
mysql> set @v = 'a';
-
Query OK, 0 rows affected (0.03 sec)
-
-
mysql> select isnull(@v);
-
+------------+
-
| isnull(@v) |
-
+------------+
-
| 0 |
-
+------------+
-
1 row in set (0.01 sec)
-
-
mysql> set @v = null;
-
Query OK, 0 rows affected (0.01 sec)
-
-
mysql> select isnull(@v);
-
+------------+
-
| isnull(@v) |
-
+------------+
-
| 1 |
-
+------------+
- 1 row in set (0.01 sec)
2、ifnull(xx,yy)：如果xx是null，那么返回指定yy，否则还是返回xx。
点击(此处)折叠或打开
-
mysql> set @v = 'a';
-
Query OK, 0 rows affected (0.01 sec)
-
-
mysql> select ifnull(@v,'wc');
-
+-----------------+
-
| ifnull(@v,'wc') |
-
+-----------------+
-
| a |
-
+-----------------+
-
1 row in set (0.00 sec)
-
-
mysql> set @v = null;
-
Query OK, 0 rows affected (0.02 sec)
-
-
mysql> select ifnull(@v,'wc');
-
+-----------------+
-
| ifnull(@v,'wc') |
-
+-----------------+
-
| wc |
-
+-----------------+
- 1 row in set (0.01 sec)
3、nullif(xx,yy) ：如果xx=yy，则返回null，否则还是返回xx
点击(此处)折叠或打开
-
mysql> set @v = 'a';
-
Query OK, 0 rows affected (0.01 sec)
-
-
mysql> select nullif(@v,'wc');
-
+-----------------+
-
| nullif(@v,'wc') |
-
+-----------------+
-
| a |
-
+-----------------+
-
1 row in set (0.04 sec)
-
-
mysql> select nullif(@v,'a');
-
+----------------+
-
| nullif(@v,'a') |
-
+----------------+
-
| NULL |
-
+----------------+
- 1 row in set, 1 warning (0.02 sec)
4、coalesce(xx,yy,zz...)：返回一个列表里第一个非null的值
点击(此处)折叠或打开
-
mysql> set @v = 'a';
-
Query OK, 0 rows affected (0.01 sec)
-
-
mysql> select coalesce(null,@v,null,3);
-
+--------------------------+
-
| coalesce(null,@v,null,3) |
-
+--------------------------+
-
| a |
-
+--------------------------+
-
1 row in set (0.02 sec)
-
-
mysql> set @v = null;
-
Query OK, 0 rows affected (0.01 sec)
-
-
mysql> select coalesce(null,@v,null,3);
-
+--------------------------+
-
| coalesce(null,@v,null,3) |
-
+--------------------------+
-
| 3 |
-
+--------------------------+
- 1 row in set (0.01 sec)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/653220/viewspace-1815925/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
