13.2.10.8 衍生 table 格
派生 table 是在查询FROM
子句范围内生成 table 的 table 达式。例如,SELECT语句FROM
子句中的子查询是派生 table:
SELECT ... FROM (subquery) [AS] tbl_name ...
[AS] tbl_name
子句是强制性的,因为FROM
子句中的每个 table 都必须具有名称。派生 table 中的任何列都必须具有唯一的名称。
为了说明起见,假设您有此 table:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
以下是使用示例 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 |
+------+------+------+
这是另一个示例:假设您想知道分组 table 的一组总和的平均值。这不起作用:
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
)中使用的列名在外部查询中被识别。
派生 table 可以返回标量,列,行或 table。
派生 table 受以下限制:
-
派生 table 不能是相关子查询。
-
派生 table 不能包含对相同SELECT的其他 table 的引用。
-
派生 table 不能包含外部引用。这是 MySQL 的限制,而不是 SQL 标准的限制。
优化程序以不需要EXPLAIN的方式确定有关派生 table 的信息。参见第 8.2.2.4 节“通过合并或实现来优化派生 table 和视图引用”。
在某些情况下,使用EXPLAIN SELECT可能会修改 table 数据。如果外部查询访问任何 table,而内部查询调用更改了 table 的一个或多个行的存储函数,则会发生这种情况。假设数据库d1
中有两个 tablet1
和t2
,并且存储函数f1
修改t2
,如下所示:
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,如在输出的table
和Extra
列中所示。以下嵌套的SELECT也是如此:
mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
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引用了任何 table,则优化器也会在子查询中执行该语句,结果是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())));