Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is CTE grammar support in the new features of MySQL8.0. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

CTE (common table expression), when creating a plan, only one of the subqueries is created and the results are put into the temporary table, and the other subqueries directly use the temporary table, aiming at the situation that the same from subquery occurs many times in SQL. For example, with as / * + materialize*/ usage is often used in Oracle.

First, let's take a look at the working process of simple non-recursive CTE

CREATE TABLE t (an int); INSERT INTO t VALUES (1), (2); mysql > WITH abc as (SELECT * FROM t) SELECT * FROM abc +-+ | a | +-+ | 1 | | 2 | number of rows returned: [2], time: 9 ms.-- in order to see the optimization process of OPTIMIZER clearly, let's disable the derived_merge feature for a while. Mysql > SET OPTIMIZER_SWITCH='derived_merge=off'; executed successfully, time consuming: 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 | | + -number of rows returned: [2] Time: 9 ms.mysql > SET OPTIMIZER_SWITCH='derived_merge=on' Successful execution, time consuming: 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 | | + -+ number of rows returned: [1] Time: 9 ms.

Ah!

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}]}} | about the new features of MySQL8.0 So much for sharing what is CTE syntax support in I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.