MySQL :: MySQL 8.0 Reference Manual :: 14.5 Flow Control Functions (original) (raw)

Table 14.7 Flow Control Operators

• 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`_=_`comparevalue`_ 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 noELSE part.
  Note
  The syntax of the CASE operator described here differs slightly from that of the SQLCASE statement described inSection 15.6.5.1, “CASE Statement”, for use inside stored programs. TheCASE statement cannot have anELSE NULL clause, and it is terminated with END CASE instead ofEND.
  The return type of a CASE expression result is the aggregated type of all result values:
  • If all types are numeric, the aggregated type is also numeric:
    * If at least one argument is double precision, the result is double precision.
    * Otherwise, if at least one argument isDECIMAL, the result isDECIMAL.
    * Otherwise, the result is an integer type (with one exception):
    * If all integer types are all signed or all unsigned, the result is the same sign and the precision is the highest of all specified integer types (that is,TINYINT,SMALLINT,MEDIUMINT,INT, orBIGINT).
    * If there is a combination of signed and unsigned integer types, the result is signed and the precision may be higher. For example, if the types are signed INT and unsigned INT, the result is signedBIGINT.
    * The exception is unsignedBIGINT combined with any signed integer type. The result isDECIMAL with sufficient precision and scale 0.
  • If all types are BIT, the result is BIT. Otherwise,BIT arguments are treated similar to BIGINT.
  • If all types are YEAR, the result is YEAR. Otherwise,YEAR arguments are treated similar toINT.
  • If all types are character string (CHAR orVARCHAR), the result isVARCHAR with maximum length determined by the longest character length of the operands.
  • If all types are character or binary string, the result isVARBINARY.
  • SET andENUM are treated similar toVARCHAR; the result isVARCHAR.
  • If all types are JSON, the result is JSON.
  • If all types are temporal, the result is temporal:
    * If all temporal types areDATE,TIME, orTIMESTAMP, the result is DATE,TIME, orTIMESTAMP, respectively.
    * Otherwise, for a mix of temporal types, the result isDATETIME.
  • If all types are GEOMETRY, the result is GEOMETRY.
  • If any type is BLOB, the result is BLOB.
  • For all other type combinations, the result isVARCHAR.
  • Literal NULL operands are ignored for type aggregation.

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`_ IS NOT NULL), IF() returns expr2. Otherwise, it returns expr3.
  If only one of expr2 or_expr3_ is explicitlyNULL, the result type of theIF() function is the type of the non-NULL expression.
  The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:
  • If expr2 or_expr3_ produce a string, the result is a string.
    If expr2 and_expr3_ are both strings, the result is case-sensitive if either string is case-sensitive.
  • If expr2 or_expr3_ produce a floating-point value, the result is a floating-point value.
  • If expr2 or_expr3_ produce an integer, the result is an integer.

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 notNULL,IFNULL() returns_expr1; otherwise it returnsexpr2_.

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 ofIFNULL(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   |     |         |       |  
+-------+--------------+------+-----+---------+-------+  

In this example, the type of the test column is VARBINARY(4) (a string type).

• NULLIF(expr1,expr2)
  Returns NULL if_`expr1`_ =_`expr2`_ is true, otherwise returns expr1. This is the same asCASE 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  

Note
MySQL evaluates expr1 twice if the arguments are not equal.

The handling of system variable values by these functions changed in MySQL 8.0.22. For each of these functions, if the first argument contains only characters present in the character set and collation used by the second argument (and it is constant), the latter character set and collation is used to make the comparison. In MySQL 8.0.22 and later, system variable values are handled as column values of the same character and collation. Some queries using these functions with system variables that were previously successful may subsequently be rejected with Illegal mix of collations. In such cases, you should cast the system variable to the correct character set and collation.