VALUES is a DML statement introduced in MySQL 8.0.19 which returns a set of one or more rows as a table. In other words, it is a table value constructor which also functions as a standalone SQL statement.
VALUES row_constructor_list [ORDER BY column_designator] [LIMIT BY number]
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
value_list:
value[, value][, ...]
column_designator:
column_index- The VALUES statement is permissive regarding data types of column values; you can mix types within the same column, as shown here:
mysql> VALUES ROW("q", 42, '2019-12-18'),
-> ROW(23, "abc", 98.6),
-> ROW(27.0002, "Mary Smith", '{"a": 10, "b": 25}');
+----------+------------+--------------------+
| column_0 | column_1 | column_2 |
+----------+------------+--------------------+
| q | 42 | 2019-12-18 |
| 23 | abc | 98.6 |
| 27.0002 | Mary Smith | {"a": 10, "b": 25} |
+----------+------------+--------------------+
3 rows in set (0.00 sec)- VALUES can be used in many cases where you could employ SELECT, including those listed here:
- With union
- In joins
- In place of VALUES() in an INSERT or REPLACE statement
- In place of the source table in CREATE TABLE ... SELECT and CREATE VIEW ... SELECT.
