In the docs for
STRICT_ALL_TABLES it states,
STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.
What does this mean though?
CREATE TABLE foo ( a int NOT NULL ); INSERT INTO foo VALUES (NULL), (NULL);
This does what everyone would reasonably expect a totally insane database to do,
SELECT * FROM foo; +---+ | a | +---+ | 0 | | 0 | +---+ 2 rows in set (0.00 sec)
But, I expected a multi-row
INSERT to insert the non-violating rows that are seen first,
SET sql_mode='STRICT_ALL_TABLES'; INSERT INTO foo VALUES (5), (NULL);
To insert just one row with
5, as per the docs in
STRICT_ALL_TABLES. Instead it does,
ERROR 1048 (23000): Column 'a' cannot be null
How do I interpret the docs to
STRICT_ALL_TABLES when they say earlier rows .. partial update and "single-row statements". And why is MySQL doing something less horrible then documented? I'm pleasantly surprised.