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
22
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
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
2
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
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
22
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
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
2
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;