MySQL：将 NULL 类型转换为 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1441333/
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
MySQL: Typecasting NULL to 0
提问by Pierre Spring
Let us suppose the following table (e.g. a result of several inner join statements):
让我们假设下表（例如几个内部连接语句的结果）：
id | column_1 | column_2
------------------------
1 | 1 |
2 | 2 | 2
3 | | 3
Which you could for example get from the following statement:
例如，您可以从以下语句中获得：
select a.id, t1.column_1, t2.column_2
from a
left join t1 on a.id = t1.id
left join t2 on a.id = t2.id
Now, if i'd like to sum up t1.column_1 and t2.column_2 as follows
现在，如果我想总结 t1.column_1 和 t2.column_2 如下
select
a.id,
t1.column_1,
t2.column_2,
(t1.column_1 + t2.column_2) as cumulated
from a
left join t1 on a.id = t1.id
left join t2 on a.id = t2.id
The reslut will look as follows:
结果如下：
id | column_1 | column_2 | cumulated
------------------------------------
1 | 1 | NULL | NULL
2 | 2 | 2 | 4
3 | NULL | 3 | NULL
My question basically is: is there a way to typecast NULL into 0 in order to do some math?
我的问题基本上是：有没有办法将 NULL 类型转换为 0 以进行一些数学运算？
I have tried CONVERT(t1.column_1, SIGNED)and CAST(t1.column_1 as SIGNED), but a NULLstays a NULL.
我试过CONVERT(t1.column_1, SIGNED)和CAST(t1.column_1 as SIGNED)，但NULL仍然是NULL。
回答by David Andres
Use IFNULL(column, 0)to convert the column value to zero. Alternatively, the COALESCE function will do the same thing, except (1) COALESCEis ANSI-compliant, IFNULLis not, and (2) COALESCEtakes an arbitrary number of columns/values and will return the first non-null value passed to it.
使用IFNULL(column, 0)的列值转换到零。或者，COALESCE 函数会做同样的事情，除了 (1)COALESCE符合 ANSI，IFNULL不是，以及 (2)COALESCE接受任意数量的列/值并返回传递给它的第一个非空值。
