2

The fields are nullable, so they do allow nulls.

Why would MySQL convert "" to 0 instead of NULL?

Is there a way to force it to be NULL instead?

2
  • Are you getting an error message like ERROR 1366 (HY000): Incorrect decimal value: '' for column 'price' at row 1 ? Dec 29, 2014 at 23:35
  • 1
    Empty string is no more like NULL than it is like 0 and arguably even less so. If you want null, why exactly would you not insert null? Jun 29, 2015 at 4:06

1 Answer 1

2

This seems be an on-going bug although it is not considered a bug, such as Bug #73041: Error 1366 when loading null into columns with decimal type using strict mode

According to Oracle's MySQL Documentation

If you are not using strict mode, then whenever you insert an “incorrect” value into a column, such as a NULL into a NOT NULL column or a too-large numeric value into a numeric column, MySQL sets the column to the “best possible value” instead of producing an error:

The following rules describe in more detail how this works:

If you try to store an out of range value into a numeric column, MySQL Server instead stores zero, the smallest possible value, or the largest possible value, whichever is closest to the invalid value.

For strings, MySQL stores either the empty string or as much of the string as can be stored in the column.

If you try to store a string that does not start with a number into a numeric column, MySQL Server stores 0.

That's also in the the regular MySQL Documentation.

With rules like these, that's why a zero(0) pops up for an unspecified DECIMAL value.

If you look back at the bug report, someone got around it when using \N for null values while loading a CSV file with LOAD DATA INFILE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.