Examples of using if in MySQL statements

This article mainly describes the example of using if in MySQL statements and the specific operation steps, this document demonstrates how to use the if statement in MySQL. The following describes the specific solution. I hope it will be helpful in your future study.

 
 

  
  
Select *, if (sva = 1, "male", "female") as ssva from taname where sva <> ""
 
 

Control Flow Functions

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 (best combination with PHP)> select case 1 WHEN 1 THEN 'one'
  
  
-> WHEN 2 THEN 'two' ELSE 'more' END;
  
  
-> 'One'
  
  
MySQL (best combination with PHP)> select case when 1> 0 THEN 'true' ELSE 'false' END;
  
  
-> 'True'
  
  
MySQL (best combination with PHP)> 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 (best combination with PHP)> select if (1> 2, 2, 3 );
  
  
-> 3
  
  
MySQL (best combination with PHP)> select if (1 <2, 'yes', 'no ');
  
  
-> 'Yes'
  
  
MySQL (best combination with PHP)> 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 (the best combination with PHP)> select if (0.1 );
  
  
-> 0
  
  
MySQL (best combination with PHP)> select if (0.1 <> 0, 0 );
  
  
-> 1
 
 

In the first example, IF (0.1) returns 0 because 0.1 is converted to an integer, resulting in a test 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 example of if () in a MySQL statement is used (this is important when it is stored in a temporary table). The default return value type 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 (the best combination with PHP)> select ifnull (1, 0 );

-> 1

MySQL (the best combination with PHP)> select ifnull (NULL, 10 );

-> 10

MySQL (the best combination with PHP)> select ifnull (1/0, 10 );

-> 10

MySQL (the best combination with PHP)> 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 (the best combination with PHP) must store the returned value of IFNULL () in a temporary table in internal memory:

Create table tmp select ifnull (1, 'test') AS test;

Example of using if in MySQL statements,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 (best combination with PHP)> select nullif );

-> NULL

MySQL (the best combination with PHP)> select nullif (1, 2 );

-> 1

Note: If the parameters are not equal, the value obtained for MySQL (the best combination with PHP) is expr1.