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)