The usage of isnull, ifnull, nullif is as follows:
Usage of isnull(expr):If expr is null, then isnull() returns a value of 1, otherwise it returns 0.
mysql> select isnull(1+1);
-> 0
mysql> select isnull(1/0);
-> 1
Using = for null value comparisons is usually wrong.
The isnull() function shares some of the same characteristics as the is null comparison operator. See the note about is null.
Usage of IFNULL(expr1,expr2):
If expr1 is not NULL, the return value of IFNULL() is expr1;
Otherwise, it returns a value of expr2. IFNULL() returns either a number or a 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 for IFNULL(expr1,expr2) is the more "generic" of the two expressions, in the order of STRING, REAL, or
INTEGER。 Suppose a situation where an expression-based table, or MySQL must store the return 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 is of type CHAR(4).
Usage of NULLIF(expr1,expr2):
If expr1
= expr2 is true, then 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 is the same.
mysql> SELECT
NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1
If the parameters are not equal, then MySQL gets the value expr1 twice.
|
Posted on 5/11/2018 1:37:59 PM
|
|
|
