故障008:CTE混合插入迁移改写
- 1. 问题描述
- 2. 解决方法
- 2.1 问题剖析
- 步骤1:快速定位SQL错误点
- 步骤2:大概掌握整体语句的作用和含义
- 步骤3:基于步骤2初步的适配改写,再次检验SQL执行效果,逐渐完善
- 步骤4:换写insert与with顺序位置,调整为insert ... select 格式的书写格式
- 2.2 改写SQL
- 改写1:仅改with,报错,不符合达梦语法
- 改写2:简单调换with位置,放在末尾,报错
- 改写3:最终版,改成insert...select的子查询表达式
- 3. CTE知识拓展
- 3.1 CTE优势
- 3.2 注意事项
- 3.3 CTE递归应用
- 示例1:CTE递归查询员工上下级关系图。
- 示例2:递归计数,突破递归层次
- 示例3:重复拼接字符串
1. 问题描述
近期越来越多软件开发商适配达梦数据库,中途难免会遇到各种各样的问题,毕竟从不同数据库中移植过来,各大数据库的提供的SQL写法都有独特的语法风格,加上很多开发人员不是很关心底层的东西,尽是一套ORM机制操纵数据库,一旦遇到问题,无从下手。
今日给大家普及的适配话题是关于CTE递归查询插入,CTE是绝大多数据库中非常炫酷的特性,比较明显的特点是SQL中带with关键字,可以用它完成很多骚操作,比如递归查询、子查询优化等等。值得大家注意的是CTE在不同数据库的写法有一点点差异:顺序和位置。
请大家随时适应各大数据库厂商的产品功能,与时俱进,达梦已经尽可能与主流数据库兼容语法,个别的地方靠大家翻阅手册,适当改写。
某公司一开发人员向我方询求帮助,原先数据库采用postgresql,开发语言使用python,适配过程很坎坷,因为他对数据库很多都不懂,我们只能一步一步协助他改写SQL,排查个别python写法问题。此次为大家讲解CTE与INSERT、集合操作的改写案例。
先上菜,开发人员提供的截图,试调试、改写的错误SQL:
2. 解决方法
2.1 问题剖析
步骤1:快速定位SQL错误点
以上提示明显是语法书写不符合达梦数据库格式,代码错误发生在第一行CTE别名附近。再仔细观察,达梦官方手册中涉及with的写法,从未提到recursive关键字,则这个多余的。 |
步骤2:大概掌握整体语句的作用和含义
凡是了解一点数据库CTE,基本知道CTE有两大功能:普通CTE写法与递归CTE写法。既然看到recursive和with内部的union(引用CTE表别名)子句写法,进一步断定属于递归查询CTE,则在union后追加all 关键字,完成with递归查询的完整语法书写,符合达梦数据库SQL引擎的识别和解析。 |
步骤3:基于步骤2初步的适配改写,再次检验SQL执行效果,逐渐完善
一般认为通过步骤2的调整,理论是没问题,但在实际测试过程中,仍报错。with整体语法在外层末尾只能跟select查询语句,目前在达梦数据库。 |
步骤4:换写insert与with顺序位置,调整为insert … select 格式的书写格式
进一步把with转成select表子查询方式,符合达梦数据库的SQL语法。 |
2.2 改写SQL
改写1:仅改with,报错,不符合达梦语法
报错:根本不支持with后面直接使用insert方法。
改写2:简单调换with位置,放在末尾,报错
报错:group_imply没事先声明定义,insert into先调用,报错属情理之中。
改写3:最终版,改成insert…select的子查询表达式
提取insert主干部分到前面,with和集合差集放入子查询表达式。
INSERT INTO res_groups_users_rel (gid, uid)
(
WITH group_imply(gid, hid) AS (
SELECT gid, hid
FROM res_groups_implied_rel
UNION ALL
SELECT i.gid, r.hid
FROM res_groups_implied_rel r
JOIN group_imply i ON (i.hid = r.gid)
)
SELECT i.hid, r.uid
FROM group_imply i, res_groups_users_rel r
WHERE r.gid = i.gid
AND i.gid = 5
EXCEPT
SELECT r.gid, r.uid
FROM res_groups_users_rel r
JOIN group_imply i ON (r.gid = i.hid)
WHERE i.gid = 5
)
3. CTE知识拓展
3.1 CTE优势
- 避免嵌套SQL语句的层次过多的旧式写法,使SQL语句易于阅读和维护。
- 鉴于子查询式的临时表不太适合频繁查询而又数据量大的情况,引用CTE效率高,表现在即时即用,不会存储为持久的对象(可理解为语句级的临时表,相较于派生表查询结果集存放在匿名临时表【并不是代表没有名称,数据库系统内部自动命名】中,它是被定义存放于有命名的临时表中,方便于语句中多次引用),仅在查询期间有效,并且它可以自引用,支持在同一个查询(整个SQL语句)中被引用多次。
- WITH CTE子句所定义的表名被调用两次以上,优化器会自动将 WITH CTE 子句所获取的数据放入一个临时表(特点:产生少量redo日志,不产生undo日志,数据存活周期短,用完自动释放空间)里,很多SQL调优通过这样特性和思想来提高速度。
本人将CTE语法分为普通CTE和递归CTE,递归CTE区别是在CTE语句当中增强UNION ALL递归子句部分,而此篇文章主要围绕该话题展开,而普通CTE没有特别介绍的地方,使用比较简单,但递归CTE注意点特别多。
3.2 注意事项
-
CTE递归查询语句的公用表达式的表列名必须定义
,这个与其他数据库(MySQL、SQLSERVER)的差异之处。 -
公用表达式表别名不能出现在定位点成员
(可理解为迭代起点),而在递归成员【 引用公用表达式表别名的SELECT查询语句,通过引用自身反复迭代执行,下一次迭代的数据基于上一次迭代的查询结果,当且仅当本次迭代结果为空集时才终止迭代】中有且仅能引用一次。 - CTE递归查询语句必须明确定位点成员,并且朝已知方向迭代到尽头,避免无限迭代下去。
-
递归查询的层次深度受参数CTE_MAXRECURSION影响
,默认值100,即表示允许迭代查询层次为100次。
如果现场遇到很大的结果集,迭代次数肯定会超过100,此时可修改参数值范围【1,4294967294】。 - 可以把
CTE递归查询中涉及相关子查询优化为WITH FUNCTION
形式,受CTE_OPT_FLAG参数影响。 -
CTE查询语句目前仅支持是SELECT查询语句
,有别于其他数据库(重点)。但语法上它支持任意SELECT语句,就实际情况而言,只有CTE查询语句中引用CTE表达式表别名,CTE才有意义。
3.3 CTE递归应用
示例1:CTE递归查询员工上下级关系图。
DROP TABLE IF EXISTS EMP;
CREATE TABLE EMP
(
EMPLOYEEID SMALLINT,
FIRST_NAME VARCHAR2 (30) NOT NULL,
LAST_NAME VARCHAR2 (40) NOT NULL,
TITLE VARCHAR2 (50) NOT NULL,
DEPTID SMALLINT NOT NULL,
MANAGERID INT NULL
);
INSERT INTO EMP VALUES (1, 'KEN', 'SANCHEZ', 'CHIEF EXECUTIVE OFFICER', 16, NULL);
INSERT INTO EMP VALUES (273, 'BRIAN', 'WELCKER', 'VICE PRESIDENT OF SALES', 3, 1);
INSERT INTO EMP VALUES (274, 'STEPHEN','JIANG', 'NORTH AMERICAN SALES MANAGER',3, 273);
INSERT INTO EMP VALUES (275, 'MICHAEL', 'BLYTHE', 'SALES REPRESENTATIVE',3, 274);
INSERT INTO EMP VALUES (276, 'LINDA', 'MITCHELL', 'SALES REPRESENTATIVE',3, 274);
INSERT INTO EMP VALUES (285, 'SYED', 'ABBAS', 'PACIFIC SALES MANAGER', 3, 273);
INSERT INTO EMP VALUES (286, 'LYNN', 'TSOFLIAS', 'SALES REPRESENTATIVE',3, 285);
INSERT INTO EMP VALUES (16, 'DAVID', 'BRADLEY', 'MARKETING MANAGER', 4, 273);
INSERT INTO EMP VALUES (23, 'MARY', 'GIBSON', 'MARKETING SPECIALIST', 4, 16);
COMMIT;
-- CTE递归查询输出组织的树状结构
with
cte(deptid, lvl, mgrno, empno, ename, title) as
(
select e.deptid, 0 lvl, e.managerid mgrno, e.employeeid empno, e.first_name ename, e.title
from emp e where e.managerid is null -- 定位点成员,即递归的初始启动值
union all
select e.deptid, lvl + 1 as lvl,e.managerid mgrno, e.employeeid empno, e.first_name ename, e.title
from cte x join emp e on x.empno = e.managerid -- 把x.empno看作迭代变量字段,每次传入上一轮的结果行与目标表关联。
)
select * from cte;
示例2:递归计数,突破递归层次
WITH cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;
-- 会话级:打开递归层次的最大上限
alter session set 'CTE_MAXRECURSION'=4294967294;
示例3:重复拼接字符串
WITH cte(n, str) AS
(
SELECT 1 AS n, 'a' AS str -- 咱达梦有点优势:'字符' 字符常量的数据类型默认是varchar(32767)类型。
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 10
)
SELECT * FROM cte;
-- 即时函数
WITH FUNCTION sf_repeatstr(vstr varchar, n int) RETURN VARCHAR
AS
v_str VARCHAR := '';
BEGIN
IF (vstr IS NOT NULL) OR (vstr != '') THEN
WITH cte(lvl, str) AS
(
SELECT 1 AS lvl, vstr AS str
UNION ALL
SELECT lvl + 1, CONCAT(str, vstr) FROM cte WHERE lvl < n
)
SELECT TOP 1 str INTO v_str FROM cte ORDER BY lvl DESC;
END IF;
RETURN v_str;
END;
select sf_repeatstr('帅小宝', 3);