MySQL Gotchas | INSERT INTO ... VALUE (sic)
This is the first gotcha I've found for the current 5.0 series.
INSERT INTO" seems to accept the phrase "
as equivalent to the more usual "
VALUES". While I'm not sure whether this is standard SQL, I've never encountered it before and can find no reference to this syntax on the documentation page at
mysql> CREATE TABLE testtable (id1 INT, id2 INT); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO testtable (id1) VALUE(1); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO testtable VALUE(1,2); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM testtable; +------+------+ | id1 | id2 | +------+------+ | 1 | NULL | | 1 | 2 | +------+------+ 2 rows in set (0.00 sec)
This syntax is present in at least versions 5.0.16 - 5.0.19. An earlier MySQL version (4.0.22) and other databases (PostgreSQL 8.x, Firebird 1.5) rejected this syntax.
Note: I was intending to post a bug report at bugs.mysql.com, but then I noticed it says at the bottom of the page "All entries become the exclusive editorial property of MySQL, Inc." without any further clarification. As I'm not quite surewhat ramifications this might have for writing about this behaviour here, I've decided to retain it as the exclusive editorial property of MySELF, Inc. ;-)