1.
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THENresult ...] [ELSE result] END
CASE WHEN [condition] THENresult [WHEN [condition] THEN result ...] [ELSE result] END
mysql> select case 2 when 1 then 1 when 2 then 2 else 3 end;
+-----------------------------------------------+
| case 2 when 1 then 1 when 2 then 2 else 3 end |
+-----------------------------------------------+
| 2 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select case when 1 > 0 then true else false end;
+------------------------------------------+
| case when 1 > 0 then true else false end |
+------------------------------------------+
| 1 |
+------------------------------------------+
1 row in set (0.00 sec)
2.
IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 andexpr1 <> NULL),则 IF()的返回值为expr2;否则返回值则为 expr3。类似于条件表达式
mysql> select if(1 > 0, true, false);
+------------------------+
| if(1 > 0, true, false) |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
3.
IFNULL(expr1,expr2)
假如expr1 不为 NULL,则返回值为 expr1; 否则其返回值为 expr2。
mysql> select ifnull(null, 1);
+-----------------+
| ifnull(null, 1) |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.03 sec)
mysql> select ifnull(2, 1);
+--------------+
| ifnull(2, 1) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)