Documentation Home
MySQL 8.3 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

15.2.15.8 派生表

本节讨论派生表的通用特征。有关以 LATERAL 关键字开头的侧面派生表的信息,请参阅 第 15.2.15.9 节,“侧面派生表”

派生表是查询 FROM 子句中的一个表达式,生成一个表。例如,SELECT 语句 FROM 子句中的子查询是一个派生表:

Press CTRL+C to copy
SELECT ... FROM (subquery) [AS] tbl_name ...

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

Press CTRL+C to copy
SELECT * FROM JSON_TABLE(arg_list) [AS] tbl_name ...

[AS] tbl_name 子句是强制性的,因为每个 FROM 子句中的表都必须有一个名称。派生表中的每个列必须有唯一的名称。或者,tbl_name 可以跟随一个括号中的列名列表:

Press CTRL+C to copy
SELECT ... FROM (subquery) [AS] tbl_name (col_list) ...

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

为了说明,假设您有以下表:

Press CTRL+C to copy
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

以下是使用子查询在 FROM 子句中的示例,使用示例表:

Press CTRL+C to copy
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;

结果:

Press CTRL+C to copy
+------+------+------+ | sb1 | sb2 | sb3 | +------+------+------+ | 2 | 2 | 4 | +------+------+------+

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

Press CTRL+C to copy
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

然而,以下查询提供了所需的信息:

Press CTRL+C to copy
SELECT AVG(sum_column1) FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1;

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

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

Press CTRL+C to copy
mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt; +---+---+---+---+ | 1 | 2 | 3 | 4 | +---+---+---+---+ | 1 | 2 | 3 | 4 | +---+---+---+---+

要显式提供列名,请在派生表名后跟随一个括号中的列名列表:

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

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

派生表受以下限制:

优化器以这样一种方式确定派生表的信息,以便 EXPLAIN 不需要实例化它们。见 第 10.2.2.4 节,“使用合并或实例化优化派生表、视图引用和公共表表达式”

在某些情况下,使用 EXPLAIN SELECT 可能会修改表数据。这可能发生在外部查询访问任何表,并且内部查询调用了修改某个表一行或多行的存储函数。假设有两个表 t1t2 在数据库 d1 中,并且有一个存储函数 f1,该函数修改 t2,如下所示:

Press CTRL+C to copy
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 产生任何影响,如下所示:

Press CTRL+C to copy
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 语句没有引用任何表,如输出的 tableExtra 列所示。这也是以下嵌套 SELECT 的情况:

Press CTRL+C to copy
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 被修改:

Press CTRL+C to copy
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)

派生表优化也可以与许多相关的标量子查询一起使用。有关更多信息和示例,请参阅 第 15.2.15.7 节,“相关子查询”