mysql中with recursive语法问题
在开发时,存在父子关系的表结构组织成树状结构,因此在查询时,我们需要使用with recursive进行处理,但是在mybatis中对with recursive的不正确的使用,往往回导致异常,虽然在naviacte中,执行可以有结果,但是在mybatis中却是异常。
比如下面的两个语句
with recursive series as (SELECT * from tb_name where id=#{id}
UNION SELECT t1.* from tb_name t1,series t2 where t1.id=t2.pid)
SELECT t1.*,series.`name` as parent_name FROM tb_name t1,series
WHERE series.pid = '0' and t1.id=#{id}
SELECT t1.*,t2.`name` as parent_name from (SELECT * from tb_name where id=#{id}) t1,
(with recursive series as (SELECT * from tb_name where id=#{id}
UNION SELECT t1.* from tb_name t1,series t2 where t1.id=t2.pid)
select `name` from series where pid='0') t2
实现同样的功能,第二种写法在navicate中可以执行出结果,但是在mybatis中执行却会出现org.springframework.jdbc.UncategorizedSQLException:
的异常