Example of if in a mysql statement _ MySQL

Source: Internet
Author: User
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




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:

Return value

The return value of expr2 or expr3 is a 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.

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


Mysql> select nullif (1, 2 );

-> 1

Note: If the parameters are not equal, the value obtained by MySQL twice is expr1.


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.