1、查询的SQL
需求很简单,就是从视图表dept_point_view查出一些数据,数据量也就500多条,但是查询会好事7秒多
select
ID as ID,
deptId as deptId,
DEPT_CODE as deptCode,
DEPT_NAME as deptName,
SHORT_NAME as shortName,
DEPT_ADDRESS as deptAddress,
SCORE_CONTENT as scoreContent,
SCORE_NUM as scoreNum,
(CASE
WHEN STATUS='1' THEN '已提交'
WHEN STATUS='2' THEN '已审核'
WHEN STATUS='4' THEN '已驳回'
ELSE '' END) as status
from dept_point_view
WHERE FIND_IN_SET(DEPT_ID,getChildLst(#{t.deptId,jdbcType=VARCHAR}))
AND `STATUS`!=0 and `STATUS`!=3
ORDER BY DEPT_ID limit 0,10
2、问题
开始以为是其他查询条件问题,在我设置的getChildLst(),纠结了好久,才逐步意识到有可能是FIND_IN_SET出现的问题,最后多亏了这位博主:https://blog.csdn.net/wokelv/article/details/78915502
问题得以解决
select
ID as ID,
deptId as deptId,
DEPT_CODE as deptCode,
DEPT_NAME as deptName,
SHORT_NAME as shortName,
DEPT_ADDRESS as deptAddress,
SCORE_CONTENT as scoreContent,
SCORE_NUM as scoreNum,
(CASE
WHEN STATUS='1' THEN '已提交'
WHEN STATUS='2' THEN '已审核'
WHEN STATUS='4' THEN '已驳回'
ELSE '' END) as status
from dept_point_view
,(SELECT getChildLst('6c20df3a7294480cab85e7caacfdb869') cids) t WHERE FIND_IN_SET(deptId,cids)
AND `STATUS`!=0 and `STATUS`!=3
ORDER BY DEPT_ID limit 0,10
先把函数返回来的缓存