Using the New TABLE and VALUES DML Statements in MySQL 8.0.19

MySQL 8.0.19 released two new DML statements: TABLE and VALUES . The TABLE statement is intended for scenarios that require a full table scan of a small table (e.g., routing tables, configuration tables) and can replace sub‑queries that select from such tables.

TABLE syntax :

TABLE table_name [ORDER BY column_name] [LIMIT number [OFFSET number]]

Example 1 creates a tiny table t1 with ten rows, then demonstrates a simple scan using TABLE t1 and shows that MySQL internally rewrites the statement to a regular SELECT (see the EXPLAIN TABLE t1 ORDER BY r1 LIMIT 2\G output).

create table t1 (r1 int, r2 int);
insert into t1 with recursive aa(a,b) as (
    select 1,1
    union all
    select a+1,ceil(rand()*20) from aa where a < 10
) select * from aa;

Running select * from t1; returns the ten generated rows, and table t1; produces the same result set, confirming the internal conversion.

Example 2 shows TABLE used inside a sub‑query: after cloning t1 to t2 , the query select * from t2 where (r1,r2) in (TABLE t1); filters t2 using the rows of t1 . The number of columns in the outer predicate must match the number of columns returned by the TABLE sub‑query.

create table t2 like t1;
insert into t2 table t1;
select * from t2 where (r1,r2) in (TABLE t1);

The VALUES statement creates a virtual row set, similar to PostgreSQL’s ROW construct. Its syntax allows optional ORDER BY and LIMIT BY clauses.

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

Single‑row example:

values row(1,2,3);

Multi‑row example with UNION ALL and ordering:

values row(1,2,3),row(10,9,8) union all values 
    row(-1,-2,0),row(10,29,30),row(100,20,-9) order by 1 desc;

Values can contain mixed data types, e.g., integers, dates, strings, JSON, binary:

values row(100,200,300),
    row('2020-03-10 12:14:15','mysql','test'),
    row(16.22,TRUE,b'1'),
    row(left(uuid(),8),'{"name":"lucy","age":"28"}',hex('dble'));

These generated rows can be inserted into a real table, as shown by creating t3 and loading the VALUES result set into it.

create table t3 (r1 varchar(100), r2 varchar(100), r3 varchar(100));
insert into t3 values row(100,200,300),
    row('2020-03-10 12:14:15','mysql','test'),
    row(16.22,TRUE,b'1'),
    row(left(uuid(),8),'{"name":"lucy","age":"28"}',hex('dble'));

In summary, the newly added TABLE and VALUES statements provide concise ways to perform full‑table scans, sub‑query replacements, and quick data generation directly within MySQL, simplifying many common tasks for developers and DBAs.