Sample Header Ad - 728x90

MySQL Row Constructor forms in INSERT VALUES vs. VALUES

1 vote
1 answer
411 views
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().
Asked by Dennis Williamson (164 rep)
Oct 5, 2021, 11:25 PM
Last activity: Apr 17, 2025, 12:00 PM