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?

• Dave: The insert statement will throw an error since you aren't supplying a valid value for an INT column.
• Rick James: This is a case where you could have answered your own question via a simple use case -- as Evan did. It is a good idea to get in the habit of such.
• Rick James: As for "all versions" -- The answer is probably yes for at least 4.0..8.0. However, there have been changes to the default value of sql_mode, so the apparent action may have changed.

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"?

• Lennart - Slava Ukraini: Both STRICT_TRANS_TABLES and STRICT_ALL_TABLES will prevent the "funny" behaviour.

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_,'')