Mysql 条件函数

1 IF

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

mysql> SELECT IF(1>2,2,3);  -> 3

mysql> SELECT IF(0, 1, 2);  -> 2 

mysql> SELECT IF(NULL,'yes','no');  -> 'no'

mysql> SELECT IF(STRCMP('test','test1'),'no','yes');  -> 'no'

 

2 CASE

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

The first CASE syntax returns the result for the first value=compare_value comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.

select case dayofweek(current_date())
    when 1 then "Sunday"
    when 2 then    "Monday"
    when 3 then "Tuesday"
    when 4 then "Wednsday"
    when 5 then "Thursday"
    when 6 then "Friday"
    when 7 then "Saturday"
    else "Error Day"
end as dayOfWeek

 set @brand = 'xiaomi';
select case 
    when instr(@brand, 'xiaomi') > 0 then "小米"
    when instr(@brand, 'huawei') > 0 then "华为"
    when instr(@brand, 'oppo') > 0 then "欧珀"
    else "品牌未知"
end;

select case -- only first TRUE will return
    when FALSE then "0"
    when TRUE then "1" 
    when TRUE then "2"
    else "why"
end as test; 

 

参考文档：https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html