background

MySQL 8.0.19 release released two new DML statements. One TABLE statement, one VALUES statement. Don't confuse these two sentences here. TABLE is not a generalized TABLE, but only a statement, which is applied to scenarios requiring full TABLE scanning. And the VALUES statement should not be confused with the traditional insert statement such as INSERT...VALUES. VALUES is a new statement simulating the Recordset, similar to the ROW statement of other databases such as PGSQL.

1, Application scenario

1. TABLE statement

• It is specifically used in the whole table scanning of small tables, such as routing table, configuration class table, simple mapping table, etc.

• Replace the SELECT statement of such a small table as a subquery.

1. VALUES statement

• VALUES is similar to the ROW statement of other databases, which is very useful for data generation.

2, Grammar usage

Now, for these two kinds of DML statements, combined with practical examples to illustrate its specific purpose.

2.1 TABLE statement

Specific syntax:

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

In fact, grammatically, you can sort or filter recordsets, but it's simpler and less powerful than SELECT.

Example 1

Simply build a small table y1 with 10 records.

Table t1, insert 10 records

mysql-(ytt/3305)->create table t1 (r1 int,r2 int);
Query OK, 0 rows affected (0.02 sec)

mysql-(ytt/3305)->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; 
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Simple full table scanning

mysql-(ytt/3305)->select * from t1;
+------+------+
| r1   | r2   |
+------+------+
|    1 |    1 |
|    2 |    9 |
|    3 |    9 |
|    4 |   17 |
|    5 |   17 |
|    6 |   16 |
|    7 |    6 |
|    8 |    1 |
|    9 |   10 |
|   10 |    3 |
+------+------+
10 rows in set (0.00 sec)

TABLE results

mysql-(ytt/3305)->table t1;
+------+------+
| r1   | r2   |
+------+------+
|    1 |    1 |
|    2 |    9 |
|    3 |    9 |
|    4 |   17 |
|    5 |   17 |
|    6 |   16 |
|    7 |    6 |
|    8 |    1 |
|    9 |   10 |
|   10 |    3 |
+------+------+
10 rows in set (0.00 sec)

Look at the execution plan of table

mysql-(ytt/3305)->explain table t1 order by r1 limit 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

In fact, you can see that the TABLE is internally converted to SELECT by MySQL.

mysql-(ytt/3305)->show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `ytt`.`t1`.`r1` AS `r1`,`ytt`.`t1`.`r2` AS `r2` from `ytt`.`t1` order by `ytt`.`t1`.`r1` limit 2
1 row in set (0.00 sec)

In fact, from the above simple example, we can see that TABLE is internally transformed into a common SELECT.

Example 2

The sub table applied to the sub query. Note here that the number of fields in the inner table must be the same as the number of fields filtered by the outer table.

Clone table t1 structure

mysql-(ytt/3305)->create table t2 like t1;
Query OK, 0 rows affected (0.02 sec)

Clone table t1 data

mysql-(ytt/3305)->insert into t2 table t1;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

table t1 is regarded as an internal table. table t1 has two fields, which must meet the requirements of t2 retrieval.

mysql-(ytt/3305)->select * from t2 where (r1,r2) in (table t1);
+------+------+
| r1   | r2   |
+------+------+
|    1 |    1 |
|    2 |    9 |
|    3 |    9 |
|    4 |   17 |
|    5 |   17 |
|    6 |   16 |
|    7 |    6 |
|    8 |    1 |
|    9 |   10 |
|   10 |    3 |
+------+------+
10 rows in set (0.00 sec)

Note: if the number of filtered fields is inconsistent with the number of sub tables, an error will be reported.

2.2 VALUES statement

Specific syntax:

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

VALUES statement is used for function display or quick data scene creation. The name of the result column starts with COLUMN_0, and so on. Take a simple example.

Single VALUES statement

mysql-(ytt/3305)->values row(1,2,3);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
+----------+----------+----------+
1 row in set (0.00 sec)

Multiple VALUES statements

mysql-(ytt/3305)->values row(1,2,3),row(10,9,8);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
|       10 |        9 |        8 |
+----------+----------+----------+
2 rows in set (0.00 sec)

Multiple VALUES UNION ALL

mysql-(ytt/3305)->values row(1,2,3),row(10,9,8) union all values \
	row(-1,-2,0),row(10,29,30),row(100,20,-9);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
|       10 |        9 |        8 |
|       -1 |       -2 |        0 |
|       10 |       29 |       30 |
|      100 |       20 |       -9 |
+----------+----------+----------+
5 rows in set (0.00 sec)

Sort by field subscript, starting from 1

mysql-(ytt/3305)->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 ;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|      100 |       20 |       -9 |
|       10 |        9 |        8 |
|       10 |       29 |       30 |
|        1 |        2 |        3 |
|       -1 |       -2 |        0 |
+----------+----------+----------+
5 rows in set (0.00 sec)

Types can be combined arbitrarily: bit,json,datetime,int,decimal, etc

mysql-(ytt/3305)->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'));
+---------------------+----------------------------+--------------------+
| column_0            | column_1                   | column_2           |
+---------------------+----------------------------+--------------------+
| 100                 | 200                        | 0x333030           |
| 2020-03-10 12:14:15 | mysql                      | 0x74657374         |
| 16.22               | 1                          | 0x01               |
| c86fd1a7            | {"name":"lucy","age":"28"} | 0x3634363236433635 |
+---------------------+----------------------------+--------------------+
4 rows in set (0.00 sec)

Create a new table t3 and write in the records just now

mysql-(ytt/3305)->create table t3 (r1 varchar(100),r2 varchar(100),r3 varchar(100));
Query OK, 0 rows affected (0.02 sec)

Write to table t3

mysql-(ytt/3305)->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'));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

summary

Here are two new DML statements TABLE and VALUES added after MySQL 8.0.19 is released, hoping to help you.