Description of IFNULL, NULLIF, and ISNULL usage in MySql, ifnullnullif

Description of IFNULL, NULLIF, and ISNULL usage in MySql, ifnullnullif

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

In mysql, isnull, ifnull, and nullif are used 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);-> 0mysql> 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 not NULL, 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 or MySQL must store the returned 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 type is 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, the value obtained by MySQL twice is expr1.

The above section describes how to use IFNULL, NULLIF, and ISNULL in MySql. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!