MySQL 子查询（三） 派生表、子查询错误

时间：2021-05-10 20:08:58

　　From MySQL 5.7 ref：13.2.10.8 Derived Tables

八、派生表

　　派生表是一个表达式，用于在一个查询的FROM子句的范围内生成表。

　　例如，在一个SELECT查询的FROM子句中的子查询，就是一个派生表。

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

　　[AS] tbl_name子句是必需的，因为FROM子句中的每个表都必须具有名称。且派生表中的任何列都必须具有唯一名称。

　　为了便于说明，假设现在有这样一个表：

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

　　使用示例表，在FROM子句中使用子查询：

INSERT INTO t1 VALUES (1,'',1.0);

INSERT INTO t1 VALUES (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）可以在外部查询中被识别。

　　派生表可以返回一个标量、一列数据，一行数据或者一个表。

　　派生表受这些限制的约束：

  • 派生表不能是相关子查询；
  • 派生表不能包含对同一SELECT中的其他表的引用；
  • 派生表不能包含外部引用。这是MySQL的限制，而不是SQL标准的限制。

　　See Section 8.2.2.4, “Optimizing Derived Tables and View References with Merging or Materialization”.

　　在某些情况下，使用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;

　　直接在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: 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引用任何表，优化器也会执行子查询中的语句，结果是修改了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语句（例如此处显示的语句）可能需要很长时间才能执行，因为会对t1中的每一行执行一次BENCHMARK()函数：

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

九、子查询错误

　　有些错误仅适用于子查询。本节介绍它们。

　　9.1 不被支持的子查询语法

ERROR 1235 (ER_NOT_SUPPORTED_YET)

SQLSTATE = 42000

Message = "This version of MySQL doesn't yet support

'LIMIT & IN/ALL/ANY/SOME subquery'"

　　这意味着MySQL不支持以下形式的语句：

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)

　　

　　9.2 子查询中的列数不正确

ERROR 1241 (ER_OPERAND_COL)

SQLSTATE = 21000

Message = "Operand should contain 1 column(s)"

　　这个错误会在以下情况下发生：

SELECT (SELECT column1, column2 FROM t2) FROM t1;

　　如果目的是行比较，则可以使用返回多个列的子查询。而在其他上下文中，子查询必须是标量操作数。参考前面的第五小节。

　　自己的试验如下（student表中有12行数据）：

mysql> select ( select SId from sc) from student;

ERROR 1242 (21000): Subquery returns more than 1 row

select (select SId,CId from sc) from student;

ERROR 1241 (21000): Operand should contain 1 column(s)

mysql> select ( select distinct SId from sc where SId="01") from student;

+-----------------------------------------------+

| ( select distinct SId from sc where SId="01") |

+-----------------------------------------------+

| 01                                            |

| 01                                            |

| 01                                            |

| 01                                            |

| 01                                            |

| 01                                            |

| 01                                            |

| 01                                            |

| 01                                            |

| 01                                            |

| 01                                            |

| 01                                            |

+-----------------------------------------------+
12 rows in set (0.00 sec)

　　9.3 子查询中的行数不正确

ERROR 1242 (ER_SUBSELECT_NO_1_ROW)

SQLSTATE = 21000

Message = "Subquery returns more than 1 row"

　　对于子查询必须最多返回一行但返回多行的语句，会发生此错误。参考以下示例：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

　　如果SELECT column1 FROM t2只返回一行，则前一个查询将起作用。如果子查询返回多行，则会发生错误1242。

　　这种情况下，可以这么修改：

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);

　　9.4 子查询中的表使用不正确：

Error 1093 (ER_UPDATE_TABLE_USED)

SQLSTATE = HY000

Message = "You can't specify target table 'x'

for update in FROM clause"

　　当一个语句尝试修改表，而这个语句的子查询却从同一个表执行SELECT操作：

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

　　您可以在UPDATE语句中使用子查询进行赋值，因为子查询在UPDATE和DELETE语句以及SELECT语句中都是合法的。但是子查询的FROM子句中不是出现UPDATE的目标表。

　　对于事务存储引擎，子查询失败会导致整个语句失败。

　　对于非事务性存储引擎，将保留在遇到错误之前进行的数据修改。

相关文章