MySQL8.0新特性中什么是CTE语法支持

这篇文章将为大家详细讲解有关MySQL8.0新特性中什么是CTE语法支持，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

CTE（common table expression），针对同一个FROM子查询在SQL中出现多次的情况，在创建计划的时候，只对其中一个子查询创建计划，并将结果放到临时表中，其它的子查询直接使用临时表。比如Oracle中经常使用的with as  /*+materialize*/ 用法。

首先，我们看一下简单非递归的CTE的工作过程

CREATE TABLE t(a int);
INSERT INTO t VALUES(1),(2);
mysql>WITH abc as
(SELECT * FROM t)
SELECT * FROM abc;
+-------------+
| a           |
+-------------+
|           1 |
|           2 |
+-------------+
返回行数：[2]，耗时：9 ms.
--为了清楚的看到OPTIMIZER的优化过程，我们先暂且关闭derived_merge特性。
mysql>SET OPTIMIZER_SWITCH='derived_merge=off';
执行成功，耗时：9 ms.
mysql>explain WITH abc as (SELECT * FROM t) SELECT * FROM abc;
+----+-------------+------------+------------+------+---------------+-----+---------+-----+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-----+---------+-----+------+----------+-------+
| 1  | PRIMARY     | <derived2> |            | ALL  |               |     |         |     | 2    |      100 |       |
| 2  | DERIVED     | t          |            | ALL  |               |     |         |     | 2    |      100 |       |
+----+-------------+------------+------------+------+---------------+-----+---------+-----+------+----------+-------+
返回行数：[2]，耗时：9 ms.

mysql>SET OPTIMIZER_SWITCH='derived_merge=on';
执行成功，耗时：9 ms.
mysql>explain WITH abc as (SELECT * FROM t) SELECT * FROM abc;
+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered   | Extra |
+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+
| 1  | SIMPLE      | t     |            | ALL  |               |     |         |     | 2    |        100 |       |
+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+
返回行数：[1]，耗时：9 ms.

啊

mysql>EXPLAIN format = json WITH cte(x) as
		(SELECT * FROM t)
		SELECT * FROM 
			(SELECT * FROM cte) AS t1,
			(SELECT * FROM cte) AS t2;
-----------------------------------------
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5.65"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 2,
          "rows_produced_per_join": 2,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.52",
            "eval_cost": "0.20",
            "prefix_cost": "2.73",
            "data_read_per_join": "32"
          },
          "used_columns": [
            "x"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "2.72"
              },
              "table": {
                "table_name": "cte",
                "access_type": "ALL",
                "rows_examined_per_scan": 2,
                "rows_produced_per_join": 2,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "2.52",
                  "eval_cost": "0.20",
                  "prefix_cost": "2.73",
                  "data_read_per_join": "32"
                },
                "used_columns": [
                  "x"
                ],
                "materialized_from_subquery": {
                  "using_temporary_table": true,
                  "dependent": false,
                  "cacheable": true,
                  "query_block": {
                    "select_id": 3,
                    "cost_info": {
                      "query_cost": "0.45"
                    },
                    "table": {
                      "table_name": "t",
                      "access_type": "ALL",
                      "rows_examined_per_scan": 2,
                      "rows_produced_per_join": 2,
                      "filtered": "100.00",
                      "cost_info": {
                        "read_cost": "0.25",
                        "eval_cost": "0.20",
                        "prefix_cost": "0.45",
                        "data_read_per_join": "32"
                      },
                      "used_columns": [
                        "a"
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 2,
          "rows_produced_per_join": 4,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "2.53",
            "eval_cost": "0.40",
            "prefix_cost": "5.65",
            "data_read_per_join": "64"
          },
          "used_columns": [
            "x"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 4,
              "cost_info": {
                "query_cost": "2.72"
              },
              "table": {
                "table_name": "cte",
                "access_type": "ALL",
                "rows_examined_per_scan": 2,
                "rows_produced_per_join": 2,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "2.52",
                  "eval_cost": "0.20",
                  "prefix_cost": "2.73",
                  "data_read_per_join": "32"
                },
                "used_columns": [
                  "x"
                ],
                "materialized_from_subquery": {
                  "sharing_temporary_table_with": {
                    "select_id": 3
                  }
                }
              }
            }
          }
        }
      }
    ]
  }
} |

关于MySQL8.0新特性中什么是CTE语法支持就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。