1

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

1
  • 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.
    – Akina
    Oct 6, 2021 at 4:52

1 Answer 1

0

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);
1
  • 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". Oct 6, 2021 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.