Mysql series (10) Mysql conditional judgment

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

12.2. 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
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 a compatible collection type of any 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), then the return value of IF() is expr2; otherwise the return value Then it 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 explicitly NULL, the result type of the IF() function is the result type of a non-NULL expression.

expr1 is evaluated as an integer value, that is, if you are validating a floating point value or a string value, 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, 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 return value 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 expr2 and expr3 are both strings, and any of the strings is case-sensitive, the returned result is case-sensitive.
http://blog.knowsky.com/
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> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,yes);
        -> yes

The default result value of IFNULL(expr1,expr2) is the more "general" one of the two expressions, in 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 example, the test column is of type 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 that if the parameters are not equal, the value obtained twice by MySQL is expr1

The above is the content of Mysql series (10) Mysql conditional judgment. For more related content, please pay attention to PHP Chinese website (www.php.cn)!