MySQL 8.0-13.2.11.8 Derived Tables

This section discusses general characteristics of derived tables. For information about lateral derived tables preceded by the LATERAL keyword, see Section 13.2.11.9, “Lateral Derived Tables”.

本节讨论派生表的一般特征。有关以lateral关键字开头的横向派生表的信息，请参见第13.2.11.9节“横向派生表”。

A derived table is an expression that generates a table within the scope of a query FROM clause. For example, a subquery in a SELECT statement FROM clause is a derived table:

派生表是在查询FROM子句范围内生成表的表达式。例如，SELECT语句FROM子句中的子查询是一个派生表:

SELECT ... FROM (subquery) [AS] tbl_name ...

The JSON_TABLE() function generates a table and provides another way to create a derived table:

JSON_TABLE()函数生成一个表，并提供另一种创建派生表的方法:

SELECT * FROM JSON_TABLE(arg_list) [AS] tbl_name ...

The [AS] tbl_name clause is mandatory because every table in a FROM clause must have a name. Any columns in the derived table must have unique names. Alternatively, tbl_name may be followed by a parenthesized list of names for the derived table columns:

[AS] tbl_name子句是必需的，因为FROM子句中的每个表都必须有一个名称。派生表中的任何列都必须具有惟一的名称。或者，tbl_name后面可以跟一个带圆括号的派生表列名称列表:

SELECT ... FROM (subquery) [AS] tbl_name (col_list) ...

The number of column names must be the same as the number of table columns.

列名的数量必须与表列的数量相同。

For the sake of illustration, assume that you have this table:

为了说明问题，假设你有这样一个表:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

Here is how to use a subquery in the FROM clause, using the example table:

下面是如何使用FROM子句中的子查询，使用示例表:

INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;

Result:

+------+------+------+
| sb1  | sb2  | sb3  |
+------+------+------+
|    2 | 2    |    4 |
+------+------+------+

Here is another example: Suppose that you want to know the average of a set of sums for a grouped table. This does not work:

下面是另一个例子:假设您想知道分组表的一组和的平均值。这是行不通的:

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

However, this query provides the desired information:

但是，这个查询提供了所需的信息:

SELECT AVG(sum_column1)
  FROM (SELECT SUM(column1) AS sum_column1
        FROM t1 GROUP BY column1) AS t1;

Notice that the column name used within the subquery (sum_column1) is recognized in the outer query.

注意，子查询中使用的列名(sum_column1)在外部查询中被识别。

The column names for a derived table come from its select list:

派生表的列名来自它的选择列表:

mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt;
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+

To provide column names explicitly, follow the derived table name with a parenthesized list of column names:

若要显式提供列名，请在派生表名后面加上带括号的列名列表:

mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+

A derived table can return a scalar, column, row, or table.

派生表可以返回标量、列、行或表。

Derived tables are subject to these restrictions:

派生表受以下限制:

• A derived table cannot contain references to other tables of the same SELECT (use a LATERAL derived table for that; see Section 13.2.11.9, “Lateral Derived Tables”).

• 派生表不能包含对相同SELECT的其他表的引用(对此使用横向派生表;参见13.2.11.9节“侧向派生表”)。

• Prior to MySQL 8.0.14, a derived table cannot contain outer references. This is a MySQL restriction that is lifted in MySQL 8.0.14, not a restriction of the SQL standard. For example, the derived table dt in the following query contains a reference t1.b to the table t1 in the outer query: 在MySQL 8.0.14之前，派生表不能包含外部引用。这是在MySQL 8.0.14中取消的MySQL限制，而不是SQL标准的限制。例如，下面查询中的派生表dt包含引用t1。B到表t1的外层查询:

  SELECT * FROM t1
  WHERE t1.d > (SELECT AVG(dt.a)
                  FROM (SELECT SUM(t2.a) AS a
                        FROM t2
                        WHERE t2.b = t1.b GROUP BY t2.c) dt
                WHERE dt.a > 10);

  The query is valid in MySQL 8.0.14 and higher. Before 8.0.14, it produces an error: Unknown column 't1.b' in 'where clause' 该查询在MySQL 8.0.14及更高版本中有效。在8.0.14之前，它产生一个错误:未知列't1。B ' in 'where从句'

The optimizer determines information about derived tables in such a way that EXPLAIN does not need to materialize them. See Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.优化器以一种EXPLAIN不需要具体化的方式确定派生表的信息。

It is possible under certain circumstances that using EXPLAIN SELECT modifies table data. This can occur if the outer query accesses any tables and an inner query invokes a stored function that changes one or more rows of a table. Suppose that there are two tables t1 and t2 in database d1, and a stored function f1 that modifies t2, created as shown here: 在某些情况下，使用EXPLAIN SELECT可以修改表数据。如果外部查询访问任何表，而内部查询调用更改表中的一行或多行的存储函数，就会发生这种情况。假设数据库d1中有两个表t1和t2，以及一个修改t2的存储函数f1，如下所示:

CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE FUNCTION f1(p1 INT) RETURNS INT
  BEGIN
    INSERT INTO t2 VALUES (p1);
    RETURN p1;
  END;

Referencing the function directly in an EXPLAIN SELECT has no effect on t2, as shown here: 在EXPLAIN SELECT中直接引用函数对t2没有影响，如下所示:

mysql> SELECT * FROM t2;
Empty set (0.02 sec)

mysql> EXPLAIN SELECT f1(5)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set (0.01 sec)

mysql> SELECT * FROM t2;
Empty set (0.01 sec)

This is because the SELECT statement did not reference any tables, as can be seen in the table and Extra columns of the output. This is also true of the following nested SELECT:

这是因为SELECT语句没有引用任何表，可以在输出的表和Extra列中看到。下面嵌套的SELECT也是这样:

mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1249 | Select 2 was reduced during optimization |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

However, if the outer SELECT references any tables, the optimizer executes the statement in the subquery as well, with the result that t2 is modified:

但是，如果外部SELECT引用任何表，优化器也会执行子查询中的语句，结果是t2被修改:

mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: system possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: a1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL *************************** 3. row *************************** id: 2 select_type: DERIVED table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used 3 rows in set (0.00 sec) mysql> SELECT * FROM t2; +------+ | c1 | +------+ | 5 | +------+ 1 row in set (0.00 sec)

This also means that an EXPLAIN SELECT statement such as the one shown here may take a long time to execute because the BENCHMARK() function is executed once for each row in t1:

这也意味着像这里所示的EXPLAIN SELECT语句可能会花费很长时间来执行，因为对于t1中的每一行，BENCHMARK()函数都会执行一次:

EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));

The derived table optimization can also be employed with many correlated (scalar) subqueries (MySQL 8.0.24 and later). For more information and examples, see Section 13.2.11.7, “Correlated Subqueries”.

派生的表优化也可以用于许多相关(标量)子查询(MySQL 8.0.24和更高版本)。有关更多信息和示例，请参见13.2.11.7节“相关子查询”。