Replace null with 0 in MySQL

I am get开发者_StackOverflowting NULL values in the results of an operation in MySQL.

Is there a way to convert the NULL values into the value 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.

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

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)

MySQL:

SELECT COALESCE(Mycolumn, 0);

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