MySQL函数find_in_set优化使用

• 2020-04-20
• 3237 View

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]);
    }