Take the 2-minute tour ×

(I'm using MySQL 5.1.)

Insert looks like this:

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

bar field was created as so: bar INT(11) COLLATION: (NULL) NULL: YES DEFAULT: (NULL)

Shouldn't an empty string be inserting a NULL? Not sure why I'm seeing a zero (0) being stored in the table.

share|improve this question
see also mysql "strict mode" –  ashy_32bit Jul 1 '13 at 8:57
add comment

4 Answers

up vote 4 down vote accepted

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.
share|improve this answer
#2 explains alot. Thank you. –  gravyface Aug 2 '11 at 20:42
add comment

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.

share|improve this answer
"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. –  gravyface Aug 2 '11 at 20:39
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. –  Hammerite Aug 2 '11 at 20:42
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer


