Empty string inserting a zero, not a null

Question

My insert statement looks like this:

INSERT INTO foo (bar) VALUES ('');

The bar field was created like so:

bar INT(11)
    COLLATION: (NULL)
    NULL: YES
    DEFAULT: (NULL)

MySQL version: 5.1.

Shouldn’t an empty string insert a NULL? I’m not sure why I’m seeing a zero (0) being stored in the table.

Answer 1

You're not inserting NULL into the table; you're inserting an empty string (which apparently maps to zero as an int). Remember that NULL is a distinct value in SQL; NULL != ''. By specifying any value (other than NULL), you're not inserting NULL. The default only gets used if you don't specify a value; in your example, you specified a string value to an integer column.

Answer 2

MySQL by default attempts to coerce invalid values for a column to the correct type. Here, the empty string '' is of type string, which is neither an integer nor NULL. I suggest taking the following steps:

1. Change the query to the following: INSERT INTO foo (bar) VALUES (NULL);
2. Enable strict mode in MySQL. This prevents as many unexpected type and value conversions from occurring. You will see more error messages when you try to do something MySQL doesn't expect, which helps you to spot problems more quickly.

Answer 3

Why should it be a NULL? You're providing a value that has an integer representation: empty strings convert to INT 0.

Only if you didn't provide any value would the default take over.

Answer 4

The way to do this is to not fill the field at all. only fill the ones that actually need to have a value.