Detailed explanation of IFNULL() and COALESCE() functions that replace null in mysql

This article mainly introduces to you about the IFNULL() and COALESCE() functions in mysql that replace null The relevant information is introduced in great detail through example code in the article, which has certain reference and learning value for everyone. Friends who need it can take a look below.

In MySQLisnull()The function cannot be used as a replacement for null values!

is as follows:

First there is a table named business:

SELECT ISNULL(business_name,'no business_name') AS bus_isnull FROM business WHERE id=2

If you run it directly, an error will be reported:

Error code: 1582

Incorrect parameter count in the call to native function 'isnull'

So, isnull() The function will not work in mysql. You can use ifnull() and coalesce() instead. As follows:

Use ifnull() function:

SELECT IFNULL(business_name,'no business_name') AS bus_ifnull FROM business WHERE id=2

Running result:

When the value of query is not null:

SELECT IFNULL(business_name,'no business_name') AS bus_ifnull FROM business WHERE id=1

The result is as follows:

Use the coalesce() function:

SELECT COALESCE(business_name,'no business_name') AS bus_coalesce FROM business WHERE id=2

The result is as follows:

When the query value is not null:

SELECT COALESCE(business_name,'no business_name') AS bus_coalesce FROM business WHERE id=1

Among them: coalesce() can also return the first value that is not null. As follows:

SELECT COALESCE(business_name,district_id,id) AS bus_coalesce FROM business WHERE id=2

So, how to use isnull() in mysql? The answer is to use it after where. As follows:

SELECT * FROM business WHERE ISNULL(business_name)

The result is as follows:

Similarly, is null and is not null is also used after where.

SELECT * FROM business WHERE business_name IS NULL

The results are as follows:

##

SELECT * FROM business WHERE business_name IS NOT NULL

Summarize

The above is the detailed content of Detailed explanation of IFNULL() and COALESCE() functions that replace null in mysql. For more information, please follow other related articles on the PHP Chinese website!