MySQL 将 NULL 转换为整数 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4061246/
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 Cast NULL to integer 0
提问by Francisc
How can I cast something that returns NULL to 0?
如何将返回 NULL 的内容转换为 0？
If this is my query: select col from table;would this be the right way to do it: select cast(col as unsigned integer) from table;?
如果这是我的查询：select col from table;这是正确的方法吗：select cast(col as unsigned integer) from table;？
Thank you.
谢谢你。
回答by Daniel Vassallo
You'd probably want to use the COALESCE()function:
您可能想要使用该COALESCE()功能：
SELECT COALESCE(col, 0) FROM `table`;
COALESCE()returns the first non-NULLvalue in the list, or NULLif there are no non-NULLvalues.
COALESCE()返回NULL列表中的第一个非值，或者NULL如果没有非NULL值。
Test case:
测试用例：
CREATE TABLE `table` (id int, col int);
INSERT INTO `table` VALUES (1, 100);
INSERT INTO `table` VALUES (2, NULL);
INSERT INTO `table` VALUES (3, 300);
INSERT INTO `table` VALUES (4, NULL);
Result:
结果：
+------------------+
| COALESCE(col, 0) |
+------------------+
| 100 |
| 0 |
| 300 |
| 0 |
+------------------+
4 rows in set (0.00 sec)
回答by Mike Causer
You can also use the IFNULL()function:
您还可以使用该IFNULL()功能：
SELECT IFNULL(col, 0) FROM `table`;
IFNULL(expr1, expr2)returns the first expression if it's not null, else returns the second expression.
IFNULL(expr1, expr2)如果它不为空，则返回第一个表达式，否则返回第二个表达式。
Test case:
测试用例：
CREATE TABLE `table` (id int, col int);
INSERT INTO `table` VALUES (1, 100);
INSERT INTO `table` VALUES (2, NULL);
INSERT INTO `table` VALUES (3, 300);
INSERT INTO `table` VALUES (4, NULL);
Result:
结果：
+----------------+
| IFNULL(col, 0) |
+----------------+
| 100 |
| 0 |
| 300 |
| 0 |
+----------------+
4 rows in set (0.00 sec)
