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)] ...