In MySql, IFNULL, NULLIF, and ISNULL are used in MySql to find that isnull in mysql is a little different from that in MSSQL. Now, let's briefly summarize: isnull and ifnull in MySql, the usage of nullif is as follows: www.2cto.com isnull (expr): 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 use = to compare null values 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: www.2cto.com
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 (, 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 ). NULLIF (expr1, expr2) usage: If expr1 www.2cto.com = expr2 is true, 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.
Mysql> select nullif ();-> NULL mysql> select nullif ();-> 1 if the parameters are not equal, the value obtained by MySQL twice is expr1.