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.

Solution 1

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.

Solution 2

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.

Solution 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.

Solution 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.

View more solutions

Comments

[gravyface]

"which apparently maps to zero as an int" and it does state in the MySQL manual that "For numeric types, the default is 0" but if explicitly set to NULL as the default for the column, why is it setting it to zero? The way I read how it works is that anything but an integer should be stored as NULL if the default is set to NULL.

[Hammerite]

The default for the column is only relevant if you don't supply a value. Here you have supplied a value, but it's invalid, so MySQL tries to interpret it as an integer. The interpretation it arrives at is integer 0.

[gravyface]

#2 explains alot. Thank you.

[Chris Johnson]

Strict mode is very important. Mysql defaults lean toward sloppy data. As a developer, do you really want to truncate data and not be aware of it? Ouch.