MySQL函数find_in_set优化使用
1、find_in_set()问题
find_in_set会使用全表扫描，导致查询效率很低
2、改进之前的语句
select * from `persons` where `logout` = '0' and FIND_IN_SET(unitcode, getChildList('%', 1));
---query time
---2.5s
复制代码
3、改进之后的语句
select * from `persons` inner join (select getChildList('%', 1) unitlist) x on `unitlist`=`unitlist` and FIND_IN_SET(unitcode, unitlist) where `logout` = '0';
---query time
---0.06s
复制代码
4、Laravel中的使用
public function scopeAtunit($query, $unitcode)
{
$unitcode = $unitcode ? $unitcode : '%';
return $query->join(DB::raw("(select getChildList('{$unitcode}', 1) unitlist) x"), function ($join) {
$join->on('unitlist', '=', 'unitlist')
->whereRaw('FIND_IN_SET(unitcode, unitlist)');
});
//return $query->whereRaw('FIND_IN_SET(unitcode, getChildList(?, 1))', [$unitcode]);
}
