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. 第一个CASE语法返回第一个value=compare_value为true的result。The second syntax returns the result for the first condition that is true. 第二种语法返回第一个条件为true的结果。If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.如果没有比较或条件为true，则返回ELSE之后的结果，如果没有ELSE部分，则返回NULL。

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

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)

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

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.如果expr2或expr3中只有一个显式为NULL，则If()函数的结果类型为非NULL表达式的类型。

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

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)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.如果expr1不为NULL，则IFNULL()返回expr1；否则返回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) is the more “general” of the two expressions, in the order STRING, REAL, or INTEGER. IFNULL(expr1,expr2)的默认返回类型是这两个表达式中更“一般”的一个，按STRING、REAL或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必须在临时表中内部存储IFNULL()返回的值的情况：

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

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

NULLIF(expr1,expr2)

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. 如果expr1 = expr2为true则返回NULL，否则返回expr1。This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.它等同于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