Instructions for nested use of MySQL ifnull

Nested use of MySQL ifnull

I searched online to see if there is a way to nest ifnull, but it seems that no one has mentioned this usage: most of them simply check whether a field is empty and then replace it with another value;

That is:

select ifnull(a,b) from A ;

But the problem I encountered is that there are two fields, and the scenario is probably like this:

Each store has two discount methods, vip_discount and simple_discount, which are named after the discount field found in the database; vip_discount is the main one.

This means that if vip_discount is not empty, its value will be returned. If its value is empty, it is necessary to further determine whether the simple_discount field is empty. If it is not empty, the value of simple_discount will be returned, otherwise an empty string will be returned.

Here I thought about whether I could use nested ifnull to determine whether I could achieve my goal.

After searching the Internet for a long time and not finding any similar blogs, I thought I could try it myself:

select ifnull(vip_discount,ifnull(simple_discount,"")) as discount from A;

This solves the problem. This SQL statement can also be followed by conditional statements to supplement other conditional queries.

The pitfalls of MYSQL ifnull

Without further ado, let's look at the SQL:

UPDATE
    lb_user u
SET u.user_level = (
    SELECT
        IFNULL(levelid, u.user_level)
    FROM lb_user_level
    WHERE `status` = 1 AND levelid > u.user_level
    AND (upgrade_score < u.empirical OR upgrade_total_money < u.total_consumption_money)
    ORDER BY levelid DESC
    LIMIT 1
);

At first glance, this SQL statement seems to be fine and is executed successfully. However, it fails when executed again. The reason is that the data cannot be retrieved according to the current conditions and the returned data is empty. Note that the data is empty, meaning there is no data, not that the data exists but the field is empty. After investigation, the SQL statement is modified as follows:

UPDATE
    lb_user u
SET u.user_level = IFNULL((
    SELECT
        levelid
    FROM lb_user_level
    WHERE `status` = 1 AND levelid > u.user_level
    AND (upgrade_score < u.empirical OR upgrade_total_money < u.total_consumption_money)
    ORDER BY levelid DESC
    LIMIT 1
), u.user_level);

After the modification, the execution is successful, and there is no IFNULL failure;

To summarize the reasons are:

In MYSQL, IFNULL can only determine if the data exists and is NULL, but cannot determine if the data is not found and is NULL.

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