MySql sharing of null function usage

Functions about null in MySql

IFNULL

ISNULL

NULLIF

IFNULL

Usage: IFNULL(expr1,expr2)

Description: If expr1 is not null, return expr1, otherwise return expr2

example:

sql	result
SELECT IFNULL(null,'The first parameter is null')	'The first parameter is null'
SELECT IFNULL('The first parameter is not null', 'I am the second parameter')	'The first parameter is not null'

ISNULL

Usage: ISNULL(expr)

Description: If expr is null, it returns 1, otherwise it returns 0

example:

sql	result
SELECT ISNULL(null)	1
SELECT ISNULL('parameter that is not null')	0

NULLIF

Usage: NULLIF(expr1,expr2)

Description: If expr1 is equal to expr2, it returns null. Otherwise returns exp1. Similar to CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

example:

sql	result
SELECT NULLIF(1,1)	null
SELECT NULLIF(1,2)	1

Notes on using the MySql function IFNULL

First, create a simple table for SQL statement operations

The table creation statement is as follows:

CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key' ,
`name` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'Name' ,
`score` int(4) NOT NULL DEFAULT 0 COMMENT 'score' ,
PRIMARY KEY (`id`)
);

Manually create data as follows:

Now let’s get down to business:

a. What is the function of IFNULL? The following is a simple SQL statement and result. If IFNULL(a,b), the value received by a is null, then b is returned, otherwise a is returned.

SELECT IFNULL(NULL,0);

b. You can predict the result of the following SQL statement. According to the function of IFNULL function, it should return 0, but the result is not like this.

SELECT IFNULL(score,0) FROM student WHERE ID = 4;

The returned result is null, which is inconsistent with the expected result of 0.

c. The following statement returns the correct result 0;

SELECT IFNULL((SELECT score FROM student WHERE ID = 4),0);

Summarize:

When using method b to use IFNULL, SUM functions, etc., you need to ensure that there are query records, otherwise a null value will be returned. Of course, you can also use method c to avoid returning a null value and avoid NPE exceptions in the program.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in: