INSERT of 1 wrong value -> Error; INSERT of 2 wrong values -> Warning!

Question

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

Answer 1

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)