MySQL Basic Tutorial 6 - Function Control Flow Function

Grammar:

• 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

at In the return result of the first solution, value=compare-value. The return result of the second scenario is the true result of the first scenario. If there is no matching result value, the return result is the result after ELSE. If there is no ELSE part, the return value is NULL.

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

The default return value type of a CASE expression is any compatible collection type of the return value, but the specific situation depends on the context in which it is located. If used in a string context, returns the resulting string. If used in a numeric context, the result is returned as a decimal, real, or integer value.

• IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), the return value of IF() is expr2 ; Otherwise the return value is expr3. The return value of IF() is either a numeric value or a string value, depending on the context.

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'

if expr2 or expr3 If only one is explicitly NULL, the result type of the IF() function is the result type of the non-NULL expression.

expr1 is evaluated as an integer value, that is, if you are validating a floating point value or a string value, then you should use comparison operations to check.

mysql> SELECT IF(0.1,1,0);

-> 0

mysql> SELECT IF(0.1< ;>0,1,0);

-> 1

In the first example shown, the return value of IF(0.1) is 0 because is 0.1 is converted to an integer value, causing a test of IF(0). This may not be the situation you want. In the second example, the comparison checks the original floating point value to see if it is non-zero. Use integers for comparison results.

The default return value type of IF() (this is important when it is stored in a temporary table) is calculated as follows:

Expression	Return value
expr2 or expr3 The return value is a string.	String
expr2 or expr3 The return value is a floating point value.	Floating point
expr2 or expr3 The return value is an integer.	Integer

If expr2 and expr3 are both strings, and any of the strings is case-sensitive, the returned result is case-sensitive.

• IFNULL(expr1,expr2)

IFexpr1 If it is not NULL, the return value of IFNULL() is expr1; otherwise the return value is expr2. The return value of IFNULL() is either a number or a string, depending on the context in which it is used.

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'

IFNULL(expr1,expr2) The default result value is the more "general" of the two expressions, in the order STRING, REAL, or INTEGER. Assume the case of an expression-based table, or MySQL must store the return value of IFNULL() in a temporary table in internal memory:

CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;

In this example, the test column type is CHAR(4).

• NULLIF(expr1,expr2)

ifexpr1 = expr2 is true, then the return value is NULL, otherwise the return value is expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

mysql> SELECT NULLIF(1,1);

-> NULL

mysql> SELECT NULLIF(1,2) ;

-> 1

Note that if the parameters are not equal, the value obtained twice by MySQL is expr1.

The above is the content of MySQL Basic Tutorial 6 - Function Control Process Function. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!