如果字段在MySQL中为空，则返回0

In MySQL, is there a way to set the "total" fields to zero if they are NULL?

在MySQL中，是否有一种方法可以将“total”字段设置为NULL?

Here is what I have:

以下是我的资料:

SELECT uo.order_id, uo.order_total, uo.order_status,
            (SELECT SUM(uop.price * uop.qty) 
             FROM uc_order_products uop 
             WHERE uo.order_id = uop.order_id
            ) AS products_subtotal,
            (SELECT SUM(upr.amount) 
             FROM uc_payment_receipts upr 
             WHERE uo.order_id = upr.order_id
            ) AS payment_received,
            (SELECT SUM(uoli.amount) 
             FROM uc_order_line_items uoli 
             WHERE uo.order_id = uoli.order_id
            ) AS line_item_subtotal
            FROM uc_orders uo
            WHERE uo.order_status NOT IN ("future", "canceled")
            AND uo.uid = 4172;

The data comes out fine, except the NULL fields should be 0.

数据输出良好，除了空字段应该为0。

How can I return 0 for NULL in MySQL?

如何在MySQL中为NULL返回0 ?

4 个解决方案

#1

226

Use IFNULL:

使用IFNULL:

IFNULL(expr1, 0)

From the documentation:

从文档:

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

如果expr1不是NULL，则IFNULL()返回expr1;否则返回expr2。IFNULL()返回一个数值或字符串值，具体取决于使用它的上下文。

#2

You can use coalesce(column_name,0) instead of just column_name. The coalesce function returns the first non-NULL value in the list.

您可以使用coalesce(column_name,0)而不仅仅是column_name。联合函数返回列表中的第一个非空值。

I should mention that per-row functions like this are usually problematic for scalability. If you think your database may get to be a decent size, it's often better to use extra columns and triggers to move the cost from the select to the insert/update.

我应该提到，对于可伸缩性来说，像这样的每行函数通常是有问题的。如果您认为您的数据库的大小合适，那么最好使用额外的列和触发器将成本从select转移到insert/update。

This amortises the cost assuming your database is read more often than written (and most of them are).

这将使您的数据库的读取次数超过编写(并且大多数是)。

#3

You can try something like this

你可以试试这个。

IFNULL(NULLIF(X, '' ), 0)

Attribute X is assumed to be empty if it is an empty String, so after that you can declare as a zero instead of last value. In another case, it would remain its original value.

属性X如果是空字符串，则假定为空，因此在此之后，您可以声明为0，而不是最后一个值。在另一种情况下，它将保持原来的价值。

Anyway, just to give another way to do that.

总之，换一种方法。

#4

Yes IFNULL function will be working to achieve your desired result.

是的，IFNULL函数将会实现你想要的结果。

SELECT uo.order_id, uo.order_total, uo.order_status,
        (SELECT IFNULL(SUM(uop.price * uop.qty),0) 
         FROM uc_order_products uop 
         WHERE uo.order_id = uop.order_id
        ) AS products_subtotal,
        (SELECT IFNULL(SUM(upr.amount),0) 
         FROM uc_payment_receipts upr 
         WHERE uo.order_id = upr.order_id
        ) AS payment_received,
        (SELECT IFNULL(SUM(uoli.amount),0) 
         FROM uc_order_line_items uoli 
         WHERE uo.order_id = uoli.order_id
        ) AS line_item_subtotal
        FROM uc_orders uo
        WHERE uo.order_status NOT IN ("future", "canceled")
        AND uo.uid = 4172;

#1

226