這篇文章將為大家詳細(xì)講解有關(guān)MySQL8.0新特性中什么是CTE語法支持，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。
員工經(jīng)過長期磨合與沉淀，具備了協(xié)作精神，得以通過團(tuán)隊(duì)的力量開發(fā)出優(yōu)質(zhì)的產(chǎn)品。創(chuàng)新互聯(lián)建站堅(jiān)持“專注、創(chuàng)新、易用”的產(chǎn)品理念，因?yàn)椤皩Ｗ⑺詫I(yè)、創(chuàng)新互聯(lián)網(wǎng)站所以易用所以簡單”。公司專注于為企業(yè)提供成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)、微信公眾號(hào)開發(fā)、電商網(wǎng)站開發(fā)，微信小程序定制開發(fā)，軟件按需規(guī)劃網(wǎng)站等一站式互聯(lián)網(wǎng)企業(yè)服務(wù)。
CTE（common table expression），針對(duì)同一個(gè)FROM子查詢?cè)赟QL中出現(xiàn)多次的情況，在創(chuàng)建計(jì)劃的時(shí)候，只對(duì)其中一個(gè)子查詢創(chuàng)建計(jì)劃，并將結(jié)果放到臨時(shí)表中，其它的子查詢直接使用臨時(shí)表。比如Oracle中經(jīng)常使用的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 | +-------------+ 返回行數(shù)：[2]，耗時(shí)：9 ms. --為了清楚的看到OPTIMIZER的優(yōu)化過程，我們先暫且關(guān)閉derived_merge特性。 mysql>SET OPTIMIZER_SWITCH='derived_merge=off'; 執(zhí)行成功，耗時(shí)：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 | | +----+-------------+------------+------------+------+---------------+-----+---------+-----+------+----------+-------+ 返回行數(shù)：[2]，耗時(shí)：9 ms.
mysql>SET OPTIMIZER_SWITCH='derived_merge=on'; 執(zhí)行成功，耗時(shí)：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 | | +----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+ 返回行數(shù)：[1]，耗時(shí)：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
}
}
}
}
}
}
}
]
}
} |關(guān)于MySQL8.0新特性中什么是CTE語法支持就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
