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.

see also mysql "strict mode" – Ashkan Kh. Nazary Jul 01 '13 at 08:57 — Ashkan Kh. Nazary, Jul 01 '13 at 08:57

Hammerite · Accepted Answer · 2011-08-02T20:43:53.187

12

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:

Change the query to the following: INSERT INTO foo (bar) VALUES (NULL);
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.

edited Aug 02 '11 at 20:43

answered Aug 02 '11 at 20:38

Hammerite

21,755
6
70
91

#2 explains alot. Thank you. – gravyface Aug 02 '11 at 20:42
1

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. – Chris Johnson Jan 31 '16 at 20:17

score 6 · Answer 2 · edited Sep 02 '14 at 18:47

6

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.

edited Sep 02 '14 at 18:47

Malachi

3,205
4
29
46

answered Aug 02 '11 at 20:30

Paul Sonier

38,903
3
77
117

"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 02 '11 at 20:39
3

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 02 '11 at 20:42

score 3 · Answer 3 · answered Aug 02 '11 at 20:33

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.

answered Aug 02 '11 at 20:33

Lightness Races in Orbit

378,754
76
643
1,055

score 2 · Answer 4 · answered Aug 02 '11 at 20:31

2

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

answered Aug 02 '11 at 20:31

Manuel

10,153
5
41
60

Empty string inserting a zero, not a null

4 Answers4

Linked