April 4, 2006

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 "VALUE" 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 http://dev.mysql.com/doc/refman/5.0/en/insert.html.

For example:

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. ;-)

Posted at 6:57 AM

now it's part of the documentation, you can use VALUES or VALUE:

[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[, col_name=expr] ... ]
Posted by: bence | 2009-09-07 10:36
I would love to know why you would use VALUE - obviously it is singular, but as VALUES works for singular it seems a bit odd. I'm glad I found this post though as I found a use of the singular in an application and thought I was going mad.
Posted by: Steve Fenton | 2011-09-15 13:47
It was a plain and simple typo of the sort you don't see initially, but notice later when reviewing code and start thinking "whoops, this query has been wrong all this time and no-one noticed?". Until you try it and find it does actually work.
Posted by: Ian Lawrence Barwick | 2011-09-18 01:28