派生表

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

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

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

该JSON_TABLE()函数生成一个表并提供另一种方法来创建派生表：

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

该子句是强制性的，因为子句中的每个表都必须具有名称。派生表中的任何列都必须具有唯一的名称。或者，可以在后面加上派生表列的名称的括号列表：[AS]tbl_nameFROMtbl_name

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

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

为了说明起见，假定您具有此表：

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

以下是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;

结果：

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

这是另一个示例：假设您想知道分组表的一组和的平均值。这不起作用：

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

但是，此查询提供了所需的信息：

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

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

派生表的列名称来自其选择列表：

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

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

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

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

派生表受以下限制：

派生表不能包含对相同表的其他表的引用SELECT（LATERAL为此，请使用派生表；请参见“横向派生表”）。
在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);
```
该查询在MySQL 8.0.14及更高版本中有效。在8.0.14之前，它会产生错误：Unknown column 't1.b' in 'where clause'

优化器以EXPLAIN不需要实现它们的方式来确定有关派生表的信息。请参见“通过合并或实现来优化派生表，视图引用和公用表表达式”。

在某些情况下，使用EXPLAIN SELECT可能会修改表数据。如果外部查询访问任何表，而内部查询调用更改了表的一个或多个行的存储函数，则会发生这种情况。假设在数据库中有两个表t1和，并创建了一个存储函数Modifys ，如下所示：t2d1f1t2

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;

直接在中引用该函数对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)

这是因为该SELECT语句未引用任何表，如在输出的table和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)

但是，如果外部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)

这也意味着EXPLAIN SELECT诸如此处所示的语句可能需要花费很长时间才能执行，因为该BENCHMARK()函数对以下内容中的每一行执行一次t1：

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