Welcome to the MySQL monitor. Commands end with ; or\g.
Your MySQL connection id is2
Server version: 5.0.41-log Source distribution
mysql>CREATETABLEnotnulltest (id INT, test TEXTNOTNULL);
Query OK, 0 rows affected (0.02 sec)
mysql>INSERTINTOnotnulltest (id, test) VALUES (1, NULL);
ERROR 1048 (23000): Column'test' cannot be null
mysql>INSERTINTOnotnulltest (id, test) VALUES (1, NULL), (2, NULL);
Query OK, 2 rows affected, 2warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql>SELECT id, test FROM notnulltest;
+------+------+| id | test |+------+------+|1|||2||+------+------+2 rows inset (0.00 sec)
mysql>SELECT id, test FROM notnulltest WHERE test ISNULL;
Empty set (0.00 sec)
So whats going on here?
Because MySQL had no transactions (in the past), it tries to avoid any error in multi-column inserts. Without transactions, the insert cannot be rolled back, therefore an error would leave an unknown number of inserted rows around and the application had to deal with the problem. For MySQL it seems a far better solution to modify your data so it fit’s the requirements.
Worth mentioning, this is the default behaviour in a recent (5.0.41) MySQL version, installed from source, with no config modifications.