MySQL – Assigning Empty String to INT NULL Field

datatypesMySQLtype conversion

Point me a reference what happens if empty string ('') is assigned to a INT NULL field.

Does it become NULL or 0?

Do all versions (starting from 5) of MySQL do the same?

Best Answer

It depends on the sql_mode you're running as. If the mode isn't strict then you'll get the inserted value to be the same as the coercion to that type, 

SELECT CAST('' AS int);
+-----------------+
| CAST('' AS int) |
+-----------------+
|               0 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

This however is insanity, as you can see..

CREATE TABLE foo ( id int, a int, b int NOT NULL );

INSERT INTO foo VALUES
  (1,1,NULL),
  (2,NULL,''),
  (3,'',NULL),
  (4,NULL,NULL);

SELECT * FROM foo;
+------+------+---+
| id   | a    | b |
+------+------+---+
|    1 |    1 | 0 |
|    2 | NULL | 0 |
|    3 |    0 | 0 |
|    4 | NULL | 0 |
+------+------+---+
4 rows in set (0.00 sec)

So we can tell MySQL to try to be less of a joke, and more like a real database, like PostgreSQL

SET sql_mode='strict_all_tables';

And then,

TRUNCATE foo;

INSERT INTO foo VALUES
  (1,1,NULL),
  (2,NULL,''),
  (3,'',NULL),
  (3,NULL,NULL);

But this time we get,

ERROR 1048 (23000): Column 'b' cannot be null

For more information see,

Related Question