Example of if in a mysql statement _ MySQL

Example of if in mysql statement bitsCN.com

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

12.2. 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> 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 );

-> 0

Mysql> select if (0.1 <>, 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 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.
Http://blog.knowsky.com/
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 value obtained by MySQL twice is expr1.

BitsCN.com