Movatterモバイル変換

Table 14.7 Flow Control Operators

• CASEvalue WHENcompare_value THENresult [WHENcompare_value THENresult ...] [ELSEresult] END

  CASE WHENcondition THENresult [WHENcondition THENresult ...] [ELSEresult] END

  The firstCASE syntax returns theresult for the firstvalue=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 afterELSE is returned, orNULL if there is noELSE part.

  Note

  The syntax of theCASEoperator described here differs slightly from that of the SQLCASEstatement described inSection 15.6.5.1, “CASE Statement”, for use inside stored programs. TheCASE statement cannot have anELSE NULL clause, and it is terminated withEND CASE instead ofEND.

  The return type of aCASE 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 signedINT and unsignedINT, 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 areBIT, the result isBIT. Otherwise,BIT arguments are treated similar toBIGINT.

  • If all types areYEAR, the result isYEAR. 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 areJSON, the result isJSON.

  • If all types are temporal, the result is temporal:

    • If all temporal types areDATE,TIME, orTIMESTAMP, the result isDATE,TIME, orTIMESTAMP, respectively.

    • Otherwise, for a mix of temporal types, the result isDATETIME.

  • If all types areGEOMETRY, the result isGEOMETRY.

  • If any type isBLOB, the result isBLOB.

  • For all other type combinations, the result isVARCHAR.

  • LiteralNULL 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)

  Ifexpr1 isTRUE (expr1 <> 0 andexpr1 IS NOT NULL),IF() returnsexpr2. Otherwise, it returnsexpr3.

  Note

  There is also anIFstatement, which differs from theIF()function described here. SeeSection 15.6.5.2, “IF Statement”.

  If only one ofexpr2 orexpr3 is explicitlyNULL, the result type of theIF() function is the type of the non-NULL expression.

  The default return type ofIF() (which may matter when it is stored into a temporary table) is calculated as follows:

  • Ifexpr2 orexpr3 produce a string, the result is a string.

    Ifexpr2 andexpr3 are both strings, the result is case-sensitive if either string is case-sensitive.

  • Ifexpr2 orexpr3 produce a floating-point value, the result is a floating-point value.

  • Ifexpr2 orexpr3 produce an integer, the result is an integer.

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

• IFNULL(expr1,expr2)

  Ifexpr1 is notNULL,IFNULL() returnsexpr1; otherwise it returnsexpr2.

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

  The default return type ofIFNULL(expr1,expr2) is the more“general” of the two expressions, in the orderSTRING,REAL, orINTEGER. Consider the case of a table based on expressions or where MySQL must internally store a value returned byIFNULL() 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 thetest column isVARBINARY(4) (a string type).

• NULLIF(expr1,expr2)

  ReturnsNULL ifexpr1 =expr2 is true, otherwise returnsexpr1. This is the same asCASE WHENexpr1 =expr2 THEN NULL ELSEexpr1 END.

  The return value has the same type as the first argument.

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

  Note

  MySQL evaluatesexpr1 twice if the arguments are not equal.