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     |  |            | 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语法支持就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。