MySQL8 以上的 CTE

 

CTE 公用表表达式(通用表表达式)

Common Table Expression 简称CTE

 

先放操作 

# 使用的数据表和数据(测试) 使用的MySQL8.0.16  
CREATE TABLE `menu` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `menu_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '菜单名称',
  `pid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上级id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='菜单表';
INSERT INTO `menu` VALUES ('1', '一级菜单', '0');
INSERT INTO `menu` VALUES ('2', '二级菜单', '1');
INSERT INTO `menu` VALUES ('3', '三级菜单', '2');

 

# 操作起来 (关键字没有大小写，看不惯的自己美化下)
# 获取 一级菜单 的所有下级菜单id
# 之前需要代码中递归，这里直接在sql语句处理 好不好用了才知道 效率说句话没有怎么测试
# 这里也使用的是 递归，MySQL cte的递归   

with recursive r as
(select id from menu where id=1
union all 
select m.id from menu as m, r where r.id = m.pid) 
select id from r;

结果如下

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

 

既然可以获取下级的，那能不能获取上级的，答案是肯定

# 获取 三级菜单的所有上级

WITH recursive r AS (
   SELECT pid FROM menu WHERE id = 3
   UNION ALL
   SELECT m.pid FROM menu AS m
,r WHERE r.pid = m.id
) SELECT pid FROM r WHERE pid!=0;

#结果如下
+------+
| pid  |
+------+
|    2 |
|    1 |
+------+

　

上面使用的就是 MySQL8版本之后的 CTE

公用表表达式是一个命名的临时结果集，仅在单个SQL语句(例如select、insert、delete和update)的执行范围内存在。

CTE分为递归CTE和非递归CTE。

 

【CTE的语法格式】

　　CTE的主要思想就先生成临时结果集，以方便后面的使用；与临时表不同的是这个结果集的作用域不是当前session而是当前语句，对！不是session级是语句级别的

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...