Mysql的条件判断函数

一 使用IF()函数进行条件判断

mysql> SELECT IF(12,2,3),IF(1<2,'yes ','no'),IF(STRCMP('test','test1'),'no','yes');
+------------+---------------------+---------------------------------------+
| IF(12,2,3) | IF(1<2,'yes ','no') | IF(STRCMP('test','test1'),'no','yes') |
+------------+---------------------+---------------------------------------+
|          2 | yes                 | no                                    |
+------------+---------------------+---------------------------------------+
1 row in set (0.01 sec)

二 使用IFNULL()函数进行条件判断

mysql> SELECT IFNULL(1,2), IFNULL(NULL,10), IFNULL(1/0, 'wrong');          // 如果第1个参数不为NULL，则返回第1个参数，如果第1个参数为NULL，则返回第2个参数
+-------------+-----------------+----------------------+
| IFNULL(1,2) | IFNULL(NULL,10) | IFNULL(1/0, 'wrong') |
+-------------+-----------------+----------------------+
|           1 |              10 | wrong                |
+-------------+-----------------+----------------------+
1 row in set, 1 warning (0.00 sec)

三 使用CASE value WHEN语句执行分支操作

mysql> SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;         // 类似switch case语句
+------------------------------------------------------------+
| CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| two                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

四 使用CASE WHEN语句执行分支操作

mysql> SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END;         // 当某个when后值为true，返回对应位置的then后面的结果
+--------------------------------------------+
| CASE WHEN 1<0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| false                                      |
+--------------------------------------------+
1 row in set (0.00 sec)