Ifnull, nullif, and isnull usage in MySQL

I used isnull in MySQL today to find that it is a little different from MSSQL. Now, let's briefly summarize:

MySQLIsnull, ifnull, nullifThe usage is as follows:

Isnull (expr) usage:
If expr is null, the return value of isnull () is 1; otherwise, the return value is 0.
MySQL> Select isnull (1 + 1 );
-> 0
MySQL> Select isnull (1/0 );
-> 1
The comparison of null values with = is usually incorrect.

The isnull () function has the same features as the is null comparison operator. See the description of is null.

Ifnull (expr1, expr2) usage:

If expr1 is notNull, Then the returned value of ifnull () is expr1;
Otherwise, the returned value is expr2. The returned value of ifnull () is a number or 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 of ifnull (expr1, expr2) is one of the two expressions that is more "common", in the order of string, real, or
Integer. Assume that an expression-based table, orMySQLThe returned value of ifnull () in a temporary table must be stored in the internal memory:
Create Table TMP select ifnull (1, 'test') as test;
In this example, the test column type is Char (4 ).
Usage of nullif (expr1, expr2:
If expr1
= Expr2 is true, then the returned value isNullOtherwise, the returned value is expr1. This is the same as case when expr1 = expr2
ThenNullElse expr1 end is the same.
MySQL> Select
Nullif (1, 1 );

-> null
mysql select nullif (1, 2);
-> 1
If the parameters are not equal, mysql Returns expr1.