Mysql递归查询

Mysql递归查询

递归查询只支持mysql8.x以上

列如现在有一个这样Mysql评论表(comments)

当我想查询某个主评论的所有子评论时

查询

• 使用连表查询

  select * 
  from comments c1 
  left join comments c2 on c1.id = c2.main_comment_id 
  where c1.id = #{id}

• 递归查询

  with recursive t as ( 
      select * from comments where id = #{id}
      #将下面所有查询可重复合并
      union all 
      #调用自定义递归方法名‘t’查询 
      select * 
      from comments 
      join t on t.id = comments.main_comment_id
  )
  #查询递归方法
  select * from t

相比连表查询 递归查询更加灵

union all为重复合并查询语句

union 为不重复合并查询语句

将union all下查询语句(select * from comments join t on t.id = comments.main_comment_id)

查询到的表 内连

union all 上查询语句(select * from comments where id = #{id})

查询到的表

详解

当调用select * from comments where id = #{id}语句后({#id} = 1) 会查询出:

( select * from comments join t on t.id = comments.main_comment_id)

随后查询所有的表

从中筛选出main_comment_id = '1'