• 首页
  • css3教程
  • html5教程
  • jQuery手册
  • vue手册
  • php手册
  • MySQL手册
  • apache手册
  • redis手册
  • 派生表

    本节讨论派生表的一般特征。有关以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 	|
    +---	+---	+---	+---	+
    

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

    派生表受以下限制:

    • 派生表不能包含对相同表的其他表的引用SELECTLATERAL为此,请使用派生表;请参见“横向派生表”)。
    • 在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语句未引用任何表,如在输出的tableExtra列中所示。以下嵌套也是如此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())));