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

This article mainly introduces to you about the IFNULL() and COALESCE()functionsinmysqlthat replacenullThe 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, theisnull()function will not work in mysql. You can useifnull()andcoalesce()instead. As follows:

Useifnull()function:

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

Running results:

When the value ofqueryis not null:

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

The result is as follows:

Use thecoalesce()function:

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

The results are 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 useisnull()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 nullandis not nullis 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

Summary

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