在 MySQL 中将 null 替换为0

我在 MySQL 操作的结果中得到了 NULL值。

有没有办法把 NULL值转换成0？

132491 次浏览

小开

There is the COALESCE method which return the first non-null parameter, in your case :

COALESCE(field, 0)

But you can use this if you want more :

COALESCE(field1, field2, 0)

小开

最佳答案

Yes, by using COALESCE.

SELECT COALESCE(null_column, 0) AS null_column FROM whatever;

COALESCE goes through the list of values you give it, and returns the first non-null value.

小开

MySQL:

SELECT COALESCE(Mycolumn, 0);

小开

I am adding this answer because no one mentioned IFNULL function

You can use IFNULL

SELECT IFNULL(column_name, 0) FROM table_name;

IFNULL will return column's value (if it has something other than NULL) otherwise second parameter passed (in this case 0).

小开

If you messed up and have NULLs in existing table layout and want zeros, here is solution:

UPDATE `table` SET `somefield`=0 WHERE `somefield` is null

小开

you can put the 0 value into your insert input method by casting it:(int)0