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

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

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

CREATETABLEt(aint);INSERTINTOtVALUES(1),(2);mysql>WITHabcas(SELECT*FROMt)SELECT*FROMabc;+-------------+|a|+-------------+|1||2|+-------------+返回行数：[2]，耗时：9ms.--为了清楚的看到OPTIMIZER的优化过程，我们先暂且关闭derived_merge特性。mysql>SETOPTIMIZER_SWITCH='derived_merge=off';执行成功，耗时：9ms.mysql>explainWITHabcas(SELECT*FROMt)SELECT*FROMabc;+----+-------------+------------+------------+------+---------------+-----+---------+-----+------+----------+-------+|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]，耗时：9ms.

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

啊

mysql>EXPLAINformat=jsonWITHcte(x)as(SELECT*FROMt)SELECT*FROM(SELECT*FROMcte)ASt1,(SELECT*FROMcte)ASt2;-----------------------------------------|{"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":"BlockNestedLoop","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语法支持就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。