The ifnull () function in MySQL is similar to the nvl () function.

7.4.8 control flow Functions

IFNULL(expr1,expr2)

If expr1No NULL, IFNULL()Return expr1Otherwise, it returns expr2. IFNULL()Returns a number or string value, depending on the context in which it is used.

mysql> select IFNULL(1,0);        -> 1mysql> select IFNULL(0,10);        -> 0mysql> select IFNULL(1/0,10);        -> 10mysql> select IFNULL(1/0,'yes');        -> 'yes' 

IF(expr1,expr2,expr3)

If expr1Is true ( expr1<>0And expr1<>NULL), Then IF()Return expr2Otherwise, it returns expr3. IF()Returns a number or string value, depending on the context in which it is used.

mysql> select IF(1>2,2,3);        -> 3mysql> select IF(1<2,'yes','no');        -> 'yes'mysql> select IF(strcmp('test','test1'),'yes','no');        -> 'no'

expr1As an integer, it means that if you are testing a floating point or string value, you should use a comparison operation.

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

In the first case above,IF(0.1)Return0Because0.1Is converted to an integer, resulting in testingIF(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

Returns the first version. result, Where value=compare-value. In the second version, if the first condition is true, result is returned. If no matching result value exists ELSEThe result is returned. If no ELSEPart, then NULLReturned.

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