Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "trampling pit analysis of null value judgment by case when in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Preface

In the development program, when extracting data from MySQL, the syntax of case when is used to make a judgment, and a small problem is encountered in judging the null value in the process of use.

The case when in sql is somewhat similar to the switch statement in Java, which is more flexible, but the handling of Null in Mysql is a little special.

Case when syntax in Mysql:

Syntax 1:

CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list] END CASE

Syntax 2:

CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list]... [ELSE statement_list] END CASE

Note: there is a difference between the two grammars, as follows:

1: the first syntax: case_value must be an expression, such as userid%2=1 or username is null, etc. This syntax cannot be used to test NULL.

2: the second syntax, CASE, does not require variables or expressions, and evaluates the conditions after each WHEN when executed directly, and executes if it meets it.

Case study:

The table structure is as follows: a value is null, b value is 1

Mysql > SELECT NULL AS a, 1 AS b + | a | b | +-+-+ | NULL | 1 | +-+

Now, if an is null, then take b, otherwise take a

Method 1: ifnull usage

Method 2: case when usage

SELECT (CASE a WHEN an IS NULL THEN b ELSE an END) AS new, a, bFROM (SELECT NULL AS a, 1 AS b) tmp

It was found that the result was wrong, and the value of new was null instead of the 1.

Why did this mistake happen? It is caused by mixing the first syntax with the second syntax. There are two values for commission_pct after case: real or null, and commission_pct is null after when also has two values: true or false, so when case is followed by null, it can never match true or false, so the output is not null.

In this case, if you have to use syntax 1, you can rewrite it as follows:

SELECT (CASE an IS NULL WHEN TRUE THEN b ELSE an END) AS new, a, bFROM (SELECT NULL AS a, 1 AS b) tmp

You can also use syntax 2 to write:

SELECT (CASE WHEN an is NULL THEN b ELSE an END) AS new, a, bFROM (SELECT NULL AS a, 1 AS b) tmp

Note another situation where there may be errors but it is not easy to find them:

SELECT (CASE a WHEN NULL THEN b ELSE an END) AS new, a, bFROM (SELECT NULL AS a, 1 AS b) tmp

There seems to be no problem, but there is actually a problem. The reason for the problem is that null's judgment cannot be judged by =. To put it simply: the value of the case expression in syntax 1 is judged by the value of the following when, etc., but is or is not must be used in mysql.

This is the end of the crater analysis of null judgment by case when in MySQL. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.