In MySQL 8, based on my reading of the documentation, INSERT VALUES
accepts row constructors in the form:
INSERT ... VALUES(1,2,3), (4,5,6), (7,8,9);
or
INSERT ... VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9);
Either of which results in the values inserted into a table.
However, with VALUES
used by itself or in a UNION
for example (in a manner similar to SELECT
), only the form using ROW()
works.
VALUES ROW(1,2), ROW(3,4);
produces
+----------+----------+
| column_0 | column_1 |
+----------+----------+
| 1 | 2 |
| 3 | 4 |
+----------+----------+
2 rows in set (0.00 sec)
But the form with only parentheses produces a 1064 error "You have an error in your SQL syntax..." even if outer parentheses are added to group the rows.
VALUES (1,2), (3, 4);
produces
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1,2), (3, 4)' at line 1
VALUES ((1,2), (3, 4));
gives a similar error.
Why is there this inconsistency in row constructor forms between the two uses of VALUES
in MySQL? Is there a plan to address this in a future version?
By contrast, PostgreSQL works without the explicit ROW()
.