queryChildrenAreaInfo 函数
create function queryChildrenAreaInfo(areaId INT) returns varchar(1000)
begin
declare ids varchar(1000) default '';
declare tempids varchar(1000);
set tempids = areaId;
while tempids is not null do
set ids = CONCAT_WS(',',ids,tempids);
select GROUP_CONCAT(id) into tempids from sys_menu where FIND_IN_SET(parent_id,tempids)>0;
end while;
return ids;
end
sql 场景是查询app一个模块下的所有权限
select m.*,IFNULL(mr.mid,0) as bind from (select * from sys_menu,(select queryChildrenAreaInfo(#{pid}) cids) t where FIND_IN_SET(id,cids) and category = 2) as m LEFT JOIN (select mid from sys_menu_role where rid =#{rid} ) mr on m.id = mr.mid order by m.type,m.sort desc
许久想起之前做的项目要用到,怕忘了
之前记得这语句一旦数据量大了很慢
1、在调用函数的时候,取个别名,在FIND_IN_SET中直接用,这样不用再去调一遍
2、只记得相关字段建立索引,那些字段忘了。