CASE value WHEN compare_value THEN result [WHEN compare_value THEN result ...] [ELSE result] ENDCASE 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] ENDCASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END

第一个 CASE 语法返回第一个 value=compare_value 比较为真的 result。第二种语法返回第一个条件为真的结果。如果没有比较或条件为真，则返回 ELSE 之后的结果，如果没有 ELSE 部分，则返回 NULL。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.

CASE 表达式结果的返回类型是所有结果值的聚合类型：The return type of a CASE expression result is the aggregated type of all result values:

mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

IF(expr1,expr2,expr3)

如果 expr1 为 TRUE（expr1 <> 0 且 expr1 IS NOT NULL），IF() 返回 expr2。否则，返回 expr3。If expr1 is TRUE (expr1 <> 0 and expr1 IS NOT NULL), IF() returns expr2. Otherwise, it returns expr3.

如果 expr2 或 expr3 中只有一个被显式设为 NULL，则 IF() 函数的结果类型就是非 NULL 表达式的类型。If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.

IF() 的默认返回类型（当它存储到临时表中时可能会有影响）计算如下：The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:

mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

IFNULL(expr1,expr2)

如果 expr1 不是 NULL，IFNULL() 返回 expr1；否则返回 expr2。If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

The default return type of IFNULL(expr1,expr2)是两种表达式中较为“通用”的一个，顺序为STRING, REAL，或INTEGER。考虑基于表达式的表的情况，或 MySQL 必须在临时表中内部存储由IFNULL()返回的值：The default return type of IFNULL(expr1,expr2) is the more “general” of the two expressions, in the order STRING, REAL, or INTEGER. Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:

mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
mysql> DESCRIBE tmp;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| test  | varbinary(4) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+

在这个例子中，test 列的类型是VARBINARY(4)（一种字符串类型）。In this example, the type of the test column is VARBINARY(4) (a string type).

NULLIF(expr1,expr2)

如果 NULL 为真，则返回 expr1 = expr2，否则返回 expr1。这与 CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END 相同。Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

返回值与第一个参数的类型相同。The return value has the same type as the first argument.

mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1