如果字段在 MySQL 中为 null，则返回 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3997327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return 0 if field is null in MySQL
提问by Kevin
In MySQL, is there a way to set the "total" fields to zero if they are NULL?
在 MySQL 中，如果“总计”字段为 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.
数据出来的很好，除了 NULL 字段应该是0.
How can I return 0 for NULL in MySQL?
如何在 MySQL 中为 NULL 返回 0？
回答by Mark Byers
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() 返回数字或字符串值，具体取决于使用它的上下文。
回答by paxdiablo
You can use coalesce(column_name,0)instead of just column_name. The coalescefunction returns the first non-NULL value in the list.
您可以使用coalesce(column_name,0)而不仅仅是column_name. 该coalesce函数返回列表中的第一个非 NULL 值。
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 selectto the insert/update.
我应该提到像这样的每行函数通常在可扩展性方面存在问题。如果你认为你的数据库可能会成为一个体面的大小，它通常最好使用额外列和触发器从移动成本select的insert/update。
This amortises the cost assuming your database is read more often than written (and most of them are).
假设您的数据库读取次数多于写入次数（并且大多数是），这会分摊成本。
回答by Gianpaolo Papa
None of the above answers were complete for me.
If your field is named field, so the selector should be the following one:
以上答案对我来说都不完整。如果您的字段被命名field，那么选择器应该是以下之一：
IFNULL(`field`,0) AS field
For example in a SELECT query:
例如在 SELECT 查询中：
SELECT IFNULL(`field`,0) AS field, `otherfield` FROM `mytable`
Hope this can help someone to not waste time.
希望这可以帮助某人不要浪费时间。
回答by ackuser
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 是空字符串，则假定它为空，因此之后您可以声明为零而不是最后一个值。在另一种情况下，它将保持其原始值。
Anyway, just to give another way to do that.
无论如何，只是提供另一种方式来做到这一点。
回答by Krishna_K_Systematix
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;
