1
CREATE TABLE `dummy` (
    `a` TINYINT(4) NOT NULL
) ENGINE=MyISAM;

1 wrong value

INSERT INTO `dummy` (`a`) VALUES (NULL);
/* SQL Fehler (1048): Column 'a' cannot be null */
/* Nothing is stored! */

2 wrong values

INSERT INTO `dummy` (`a`) VALUES (NULL), (NULL);
/* Affected rows: 2  Gefundene Zeilen: 0  Warnungen: 2  Dauer von Abfrage: ... sec. */
SHOW WARNINGS LIMIT 5; /* Not sure where this comes from! Maybe HeidiSQL? */
/* Two entries are stored! */

Why is MySQL raising an error with the single value query, but not at the second query with two values?

Is it related to SHOW WARNINGS LIMIT 5; But where does this line come from and why is it changing the error type from Error to Warning?

MySQL V5.6.31

  • 1
    I imagine it's just a design decision to make it a warning when doing a multiple insert? – Philᵀᴹ Sep 19 '16 at 15:27
  • From a Facebook comment: strict_mode might solve the different behavior... – powtac Sep 19 '16 at 20:28
5

It is way MySQL is designed. As per the documentation:

If you try to store NULL into a column that doesn't take NULL values, an error occurs for single-row INSERT statements. For multiple-row INSERT statements or for INSERT INTO ... SELECT statements, MySQL Server stores the implicit default value for the column data type. In general, this is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. ....

I did a test to see the contents of the table (Notice that I got the warnings!):

mysql> CREATE TABLE `dummy` (
    ->     `a` TINYINT(4) NOT NULL
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `dummy` (`a`) VALUES (NULL);
ERROR 1048 (23000): Column 'a' cannot be null

mysql> INSERT INTO `dummy` (`a`) VALUES (NULL), (NULL);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
2 rows in set (0.00 sec)

mysql> select * from dummy;
+---+
| a |
+---+
| 0 |
| 0 |
+---+
2 rows in set (0.00 sec)
  • Crazy that there is a different storing behavior related to the number of items to save! Good find. This is the "What", still missing the "Why?" ;-) – powtac Sep 19 '16 at 16:30
  • Is there something like – powtac Oct 13 '16 at 11:06
  • To get the same DB behavior for single and multiple rows SET sql_mode = 'STRICT_TRANS_TABLES'; SET sql_mode = 'STRICT_ALL_TABLES'; can be used. But is there also something like: use weak behavior also on single lines? – powtac Oct 13 '16 at 11:08

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.