1

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?

  • The insert statement will throw an error since you aren't supplying a valid value for an INT column. – Dave Jul 16 '18 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 '18 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 '18 at 21:05
2

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,

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

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.