mysql 流程控制

1.

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THENresult ...] [ELSEresult] END

CASE WHEN [condition] THENresult [WHEN [condition] THENresult ...] [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)