why i can insert null into mysql field which is varchar not null

Question

I've created a table in mysql by

create table test (id int primary key not null auto_increment, vs varchar(255) not null);

when run

insert into test (vs) values (null);

It throws an error:

ERROR 1048 (23000): Column 'vs' cannot be null

But when I try to insert two rows by

insert into test (vs) values (null),(null);

It works and result is:

mysql> select * from test;
+----+----+
| id | vs |
+----+----+
|  1 |    |
|  2 |    |
+----+----+

The field vs is not nullable, I wonder whether it is a feature.

Answer 1

This is neither a bug nor a feature. It's just how it works in this case. This behavior is documented in MySQL documentation - Constraints on Invalid Data.

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. Implicit default values are discussed in Section 11.7, “Data Type Default Values”.

This happens when you have strict mode turned off.