mysql insert null data into NOT NULL column

Question

I'm confused when inserting null data (records with null field) into NOT NULL columns. The mysql table disables STRICT-SQL-MODE. According to thess two docs, it was supposed to work but the truth is not.

• https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict
• https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html#data-type-defaults-implicit

For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

• If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows are inserted.
• If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

When I insert a batch of records (with null field), for example 100 records, it works.

But when I insert only one record with null field, it failes with the message: ERROR 1048 (23000): Column 'int_without_default' cannot be null.

My question is how to insert single record with null field into NOT NULL column.

---

Here is the test case I made:

1. Create a table with two columns with 'NOT NULL' requirement:

CREATE TABLE `test_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `int_without_default` int(11) NOT NULL, 
  `int_with_default` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='for test';

Sql mode of the table

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. Inserting single record with null caused failure:

-- ERROR 1048 (23000): Column 'int_without_default' cannot be null
insert into `test_table` (`int_without_default`,`int_with_default`)
value (null, 123);

-- ERROR 1048 (23000): Column 'int_with_default' cannot be null
insert into `test_table` (`int_without_default`,`int_with_default`) 
value (123, null);

-- ERROR 1048 (23000): Column 'int_without_default' cannot be null
insert into `test_table` (`int_without_default`,`int_with_default`) 
values (null, 123);

-- ERROR 1048 (23000): Column 'int_with_default' cannot be null
insert into `test_table` (`int_without_default`,`int_with_default`) 
values (123, null);

3. But inserting multiple records with null worked. (I print the result below)

-- success
insert into `test_table` (`int_without_default`,`int_with_default`) 
values 
(null, 123),
(null, 456);

-- success
insert into `test_table` (`int_without_default`,`int_with_default`) 
values
(123, null),
(456, null);

-- ERROR 1048 (23000): Column 'int_without_default' cannot be null
insert into `test_table` (`int_without_default`,`int_with_default`) 
values
(null, null);

-- success
insert into `test_table` (`int_without_default`,`int_with_default`) 
values
(null, null),
(null, null);

4. After executing above sql statement, the content of the table:

mysql> select * from test_table;
+----+---------------------+------------------+
| id | int_without_default | int_with_default |
+----+---------------------+------------------+
|  1 |                   0 |              123 |
|  2 |                   0 |              456 |
|  3 |                 123 |                0 |
|  4 |                 456 |                0 |
|  5 |                   0 |                0 |
|  6 |                   0 |                0 |
+----+---------------------+------------------+
6 rows in set (0.00 sec)

operation screenshot