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)