Assigning empty string to INT NULL field in MySQL

Question

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?

Answer 1

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,

• What is MySQL's "Implicit Default Value" and how does it work with "strict mode"?

Answer 2

In addition to the previous answer, if you migrate or use Amazon RDS it takes the default value and it can be changed only with at least one of the following values:

ALLOW_INVALID_DATES, ANSI, ANSI_QUOTES, DB2, 
ERROR_FOR_DIVISION_BY_ZERO, HIGH_NOT_PRECEDENCE, 
IGNORE_BAD_TABLE_OPTIONS, IGNORE_SPACE, MAXDB, MSSQL, MYSQL323, 
MYSQL40, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, 
NO_BACKSLASH_ESCAPES, NO_DIR_IN_CREATE, NO_ENGINE_SUBSTITUTION, 
NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, 
NO_UNSIGNED_SUBTRACTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, 
ONLY_FULL_GROUP_BY, ORACLE, PAD_CHAR_TO_FULL_LENGTH, 
PIPES_AS_CONCAT, POSTGRESQL, REAL_AS_FLOAT, 
STRICT_ALL_TABLES, STRICT_TRANS_TABLES, TRADITIONAL

And there is no way to insert the empty setting

Answer 3

I got around this, by forcing the value to become null with the function NULLIF(_variable_,'')