Mysql的条件判断函数

忘是亡心i 2021-07-24 23:00 339阅读 0赞

**一 使用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)