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语法支持就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
分享文章：MySQL8.0新特性中什么是CTE语法支持
文章链接：http://mswzjz.cn/article/iecjdd.html