mysql control process function

Syntax:

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

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 return value, but the specific situation depends on its context. 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), then 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 only one of expr2 or expr3 is clearly NULL, then IF () The result type of the 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

as shown In the first example, IF(0.1) returns 0 because 0.1 is converted to an integer value, causing a test for 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 returns 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)

If expr1 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> ;

-> 10

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

-> 'yes'

IFNULL(expr1,expr2)'s default result value is the more "yes" of the two expressions A "generic" one, 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 memory:

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

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

NULLIF(expr1,expr2)

If expr1 = expr2 is true, 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> The resulting value is expr1.