Mysql ifnull function, mysqlifnull Function

Mysql ifnull function, mysqlifnull Function

The usage of the mysql ifnull function is described in detail below for your reference. If you have encountered similar problems in using the mysql ifnull function, take a look.

Mysql ifnull (expr1, expr2)
If expr1 is not NULL, IFNULL () returns expr1; otherwise, it returns expr2. IFNULL () returns a number or string value, depending on the context in which it is used.
Mysql> select IFNULL (1, 0 );
-> 1
Mysql> select IFNULL (0, 10 );
-> 0
Mysql> select IFNULL (1/0, 10 );
-> 10
Mysql> select IFNULL (1/0, yes );
-> Yes

IF (expr1, expr2, expr3)
IF expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF () returns expr2; otherwise, expr3 is returned. IF () returns a number or string value, depending on the context in which it is used.
Mysql> select IF (1> 2, 2, 3 );
-> 3
Mysql> select IF (1 <2, yes, no );
-> Yes
Mysql> select IF (strcmp (test, test1), yes, no );
-> No

Expr1 is calculated as an integer, which means that if you are testing a floating point or string value, you should use a comparison operation.

Mysql> select IF (0.1 );
-> 0
Mysql> select IF (0.1 <>, 0 );
-> 1

In the first case above, IF (0.1) returns 0, because 0.1 is converted to an integer, resulting in testing IF (0 ). This may not be what you expected. In the second case, compare and test the original floating point value to see if it is non-zero. The comparison result is used as an integer.

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
The first version returns result, where value = compare-value. In the second version, if the first condition is true, result is returned. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, NULL is returned.
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 usage of the mysql ifnull function is described in detail below for your reference. If you have encountered similar problems in using the mysql ifnull function, take a look.

Mysql ifnull (expr1, expr2)
If expr1 is not NULL, IFNULL () returns expr1; otherwise, it returns expr2. IFNULL () returns a number or string value, depending on the context in which it is used.
Mysql> select IFNULL (1, 0 );
-> 1
Mysql> select IFNULL (0, 10 );
-> 0
Mysql> select IFNULL (1/0, 10 );
-> 10
Mysql> select IFNULL (1/0, yes );
-> Yes

IF (expr1, expr2, expr3)
IF expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF () returns expr2; otherwise, expr3 is returned. IF () returns a number or string value, depending on the context in which it is used.
Mysql> select IF (1> 2, 2, 3 );
-> 3
Mysql> select IF (1 <2, yes, no );
-> Yes
Mysql> select IF (strcmp (test, test1), yes, no );
-> No

Expr1 is calculated as an integer, which means that if you are testing a floating point or string value, you should use a comparison operation.

Mysql> select IF (0.1 );
-> 0
Mysql> select IF (0.1 <>, 0 );
-> 1

In the first case above, IF (0.1) returns 0, because 0.1 is converted to an integer, resulting in testing IF (0 ). This may not be what you expected. In the second case, compare and test the original floating point value to see if it is non-zero. The comparison result is used as an integer.

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
The first version returns result, where value = compare-value. In the second version, if the first condition is true, result is returned. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, NULL is returned.
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