Practical application of MySQL conditional select case

The following articles mainly describe the practical application of MySQL conditional select case. This article does not closely introduce the actual application code example, it also lists the items worth your attention in the actual operation of the MySQL condition select case. The following is the specific operation content of the article.

2010-03-17 09: 20 select *, if (sva = 1, "male", "female") as ssva from tableame where id = 1Quote Control Flow Function

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 returned results of the first scheme, value = compare-value. The returned results of the second solution are the real results of the first case. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, 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 the compatible set type of any return value, but the specific situation depends on the context. If it is used in a string context, a result flavor string is returned. If it is used in the numeric context, the returned result is a decimal value, a real value, or an integer.

 
 

  
  
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 a numeric or 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 expr2 or expr3 is NULL, The result type of the IF () function is not the result type of the NULL expression.

Expr1 is calculated as an integer. That is to say, if you are verifying a floating point or string value, you should use a comparison operation for testing.

 
 

  
  
MySQL> SELECT IF(0.1,1,0);  
  
  
-> 0  
  
  
MySQL> SELECT IF(0.1<>0,1,0);  
  
  
-> 1  
 
 

MySQL condition select case in the first example, the IF (0.1) return value is 0, because 0.1 is converted to an integer, resulting in a pair of IF (0). This may not be what you want. In the second example, a comparison checks the original floating point value to see if it is a non-zero value. The comparison result uses an integer.

The default Return Value Type of IF () (which is important when it is stored in a temporary table) is calculated as follows:

Expression

Return Value

The return value of expr2 or expr3 is a string.

String

The return value of expr2 or expr3 is a floating point value.

Floating Point

The return value of expr2 or expr3 is an integer.

Integer

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

IFNULL (expr1, expr2)

If expr1 is not NULL, the returned value of IFNULL () is expr1; otherwise, the returned value is expr2. The returned value of IFNULL () is a number or 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 of IFNULL (expr1, expr2) is one of the two expressions that is more "common" in the order of STRING, REAL, or INTEGER. Assume that an expression-based table or MySQL must store the returned 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)

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> SELECT NULLIF(1,2);  
  
  
-> 1  
 
 

Note: If the parameters are not equal, the MySQL condition select case returns the value expr1 twice.