Empty string inserting a zero, not a null

mysql null zero
gravyface picture gravyface · Aug 2, 2011 · Viewed 11.7k times · Source

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

Hammerite picture Hammerite · Aug 2, 2011

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.

Related questions

How set 0 with MAX function when it is NULL?

I would like to understand how to set 0 value of the attribute when it is NULL with MAX function. For example: Name columns: number - date Values: 10 - 2012-04-04 11 - 2012-04-04 12 - 2012-04-04 13 - 2012-04-15 14 - 2012…

Read More
Mysql AVG to ignore zero

I need to perform an avg on a column, but I know that most of the values in that column will be zero. Out of all possible rows, only two will probably have positive values. How can I tell mySQL …

Read More
MySQL: selecting rows where a column is null

I'm having a problem where when I try to select the rows that have a NULL for a certain column, it returns an empty set. However, when I look at the table in phpMyAdmin, it says null for most of …

Read More