A brief discussion on the detailed explanation of the ifnull() function similar to the nvl() function in Mysql

The following editor will bring you a brief discussion of the ifnull() function similar to the nvl() function in Mysql. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

Recommended related mysql video tutorials: "mysql tutorial"

IFNULL(expr1,expr2)

If expr1 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), then IF() returns expr2, otherwise it returns expr3. IF() returns a numeric 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,&#39;yes&#39;,&#39;no&#39;);
    -> &#39;yes&#39;
mysql> select IF(strcmp(&#39;test&#39;,&#39;test1&#39;),&#39;yes&#39;,&#39;no&#39;);
    -> &#39;no&#39;

expr1 is evaluated as an integer value, which means that if you are testing floating point or string values, you should use a comparison operation to do it.

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

In the first case above, IF(0.1) returns 0 because 0.1 is converted to an integer value, causing IF(0) to be tested. This may not be what you expect. In the second case, the comparison tests the original floating point value to see if it is nonzero, and the result of the comparison 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, the result is returned. If there is no matching result value, the result in the result after ELSE is returned. If there is no ELSE part, then 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 above is a detailed explanation of the ifnull() function similar to the nvl() function in Mysql. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!