MySQL Row Constructor forms in INSERT VALUES vs. VALUES

Question

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

• Akina: Do not mix VALUES Statement which needs in row constructor as a value and hence needs in ROW() usage and INSERT Statement in INSERT .. VALUES form which allows either values list or row constructors list as a value.

Answer 1

MySQL is quite happy (and efficient) with your first form. What was right after the word "near" in the 1064 error message?

INSERT INTO table_name
 (a, b, c)
 VALUES
 (1,2,3), (4,5,6), (7,8,9);

• Dennis Williamson: Either form works with INSERT. The form without ROW does not work with bare VALUES used in a similar way to SELECT. Examples: VALUES ROW(1,2); produces a table output. VALUES (1,2); produces a 1064 error that ends with "...near '(1,2)' at line 1".