Table 12.7 Flow Control Operators
CASEsyntax returns the
resultfor the first
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
ELSEis returned, or
NULLif there is no
The syntax of the
CASEoperator described here differs slightly from that of the SQL
CASEstatement described in Section 22.214.171.124, “CASE Statement”, for use inside stored programs. The
CASEstatement cannot have an
ELSE NULLclause, and it is terminated with
END CASEinstead of
The return type of a
CASEexpression 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, 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,
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, the result is signed
If all types are character or binary string, the result is
If all types are temporal, the result is temporal:
If all types are
GEOMETRY, the result is
For all other type combinations, the result is
NULLoperands 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
expr1IS NOT NULL
expr2. Otherwise, it returns
If only one of
NULL, the result type of the
IF()function is the type of the non-
The default return type of
IF()(which may matter when it is stored into a temporary table) is calculated as follows:
expr3produce a string, the result is a string.
expr3are both strings, the result is case-sensitive if either string is case-sensitive.
expr3produce a floating-point value, the result is a floating-point value.
expr3produce 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'
expr1; otherwise it returns
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(is the more “general” of the two expressions, in the order
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
VARBINARY(4)(a string type).
is true, otherwise returns
expr1. This is the same as
expr2THEN NULL ELSE
The return value has the same type as the first argument.
mysql> SELECT NULLIF(1,1); -> NULL mysql> SELECT NULLIF(1,2); -> 1Note
expr1twice 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.