MSYQL case when THEN control process function Tutorial

Case value when [compare-value] THEN result [when [compare-value] THEN result ...] [ELSE result] When [condition] THEN result [when [condition] THEN result ...] [ELSE result] End
In the return result of the first scenario, Value=compare-value. The return result of the second scheme is the true result of the first. If there is no matching result value, the result is returned, and 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 is 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 the compatible set type of any return value, but the situation depends on the context in which it resides. If used in a string context, the result flavour string is returned. If used in a numeric context, the result is a decimal value, a real value, or an 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 the IF () is a numeric value or a string value, depending on the context in which it is located.

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 there is only one clear null in EXPR2 or EXPR3, the result type of the if () function is the result type of a non-null expression.

EXPR1 is computed as an integer value, that is, if you are validating a floating-point value or a string value, you should use a comparison operation to test it.

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 0.1 is converted to an integer value, which results in a test of if (0). This may not be the case you want. In the second example, the comparison examines the original floating-point value to see if it is a value other than 0. The comparison results with integers.

The default return value type of IF (), which is important when it is stored in a temporary table, is calculated in the following manner:

An expression
return value

The EXPR2 or EXPR3 return value is a string.
String

The EXPR2 or EXPR3 return value is a floating-point value.
Floating point

The EXPR2 or EXPR3 return value is an integer.
Integer

If both EXPR2 and EXPR3 are strings and any one of them is case-sensitive, the return result is case-sensitive.

Ifnull (EXPR1,EXPR2)
If EXPR1 is not NULL, then the return value of Ifnull () is expr1; Otherwise, its 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 '

The default result value for Ifnull (EXPR1,EXPR2) is one of the more "generic" in two expressions, in the order of string, real, or INTEGER. Suppose a table based on an expression, or MySQL must store the return value of Ifnull () in a temporary table in the internal memory:

CREATE TABLE tmp SELECT ifnull (1, ' Test ') as test;

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

Nullif (EXPR1,EXPR2)
If Expr1 = EXPR2 is established, then the return value is NULL, otherwise the return value is EXPR1. This is the same as when the case is Expr1 = expr2 THEN NULL ELSE expr1 end.

Mysql> SELECT Nullif (1,1);

-> NULL

Mysql> SELECT nullif (1,2);

-> 1

Note that if the arguments are not equal, then MySQL evaluates to two times expr1.