mysql中IF和IFNULL兩個例子

1.IFNULL語句：IFNULL（exp1, exp2）;如果exp1是null的話返回exp2，如果不是null的話返回exp1

 

代碼如下

複製代碼

mysql> SELECT IFNULL(null, 100); +-------------------+ | IFNULL(null, 100) | +-------------------+ |               100 | +-------------------+ mysql> SELECT IFNULL(0, 100); +----------------+ | IFNULL(0, 100) | +----------------+ |              0 | +----------------+ mysql> SELECT IFNULL(-10, 100); +------------------+ | IFNULL(-10, 100) | +------------------+ |              -10 | +------------------+ mysql> SELECT IFNULL(10, 100); +-----------------+ | IFNULL(10, 100) | +-----------------+ |              10 | +-----------------+ mysql> SELECT IFNULL('null', 100); +---------------------+ | IFNULL('null', 100) | +---------------------+ | null                | +---------------------+ mysql> SELECT IFNULL(false, 100); +--------------------+ | IFNULL(false, 100) | +--------------------+ |                  0 | +--------------------+ mysql> SELECT IFNULL(true, 100); +-------------------+ | IFNULL(true, 100) | +-------------------+ |                 1 | +-------------------+

2.IF語句：IF（exp1, exp2, exp3）如果exp1為true（exp1 <> 0 && exp1 <> null）

返回exp2，否則返回exp3

代碼如下

複製代碼

mysql> SELECT IF(STRCMP('str', 'str1'), 'yes', 'no'); +----------------------------------------+ | IF(STRCMP('str', 'str1'), 'yes', 'no') | +----------------------------------------+ | yes                                    | +----------------------------------------+ mysql> SELECT IF(0, 'yes', 'www.111cn.net'); +--------------------+ | IF(0, 'yes', 'no') | +--------------------+ | no                 | +--------------------+ mysql> SELECT IF(null, 'yes', 'no'); +-----------------------+ | IF(null, 'yes', 'no') | +-----------------------+ | no                    | +-----------------------+ mysql> SELECT IF('null', 'yes', 'no'); +-------------------------+ | IF('null', 'yes', 'no') | +-------------------------+ | no                      | +-------------------------+ mysql> SELECT IF(false, 'yes', 'no'); +------------------------+ | IF(false, 'yes', 'no') | +------------------------+ | no                     | +------------------------+ mysql> SELECT IF(-10, 'yes', 'no'); +----------------------+ | IF(-10, 'yes', 'no') | +----------------------+ | yes                  | +----------------------+ mysql> SELECT IF(10, 'yes', 'no'); +---------------------+ | IF(10, 'yes', 'no') | +---------------------+ | yes                 | +---------------------+ mysql> SELECT IF('0', 'yes', 'no'); +----------------------+ | IF('0', 'yes', 'no') | +----------------------+ | no                   | +----------------------+