4

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?

3
  • The insert statement will throw an error since you aren't supplying a valid value for an INT column.
    – Dave
    Jul 16, 2018 at 19:02
  • 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
    Jul 16, 2018 at 21:03
  • 1
    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.
    – Rick James
    Jul 16, 2018 at 21:05

3 Answers 3

5

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,

1
  • 1
    Both STRICT_TRANS_TABLES and STRICT_ALL_TABLES will prevent the "funny" behaviour.
    – Lennart
    Jul 16, 2018 at 19:14
1

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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.