mysql自定义排序 常用方法

博客地址：https://www.1024cc.cn

排序【指定字段】

// laravel用法：
$arrayCondition['orderByRaw] = \DB::raw("FIELD(id, ".implode(',', $ids).')'),

// sql语句:
SELECT * FROM 表名 WHERE id IN (1,2,3,4) ORDER BY FIELD(`id`,4, 2, 1, 3);

排序【按照长度】

// laravel用法：
$arrayCondition['orderByRaw'] = \DB::raw("LENGTH(name) - LENGTH(\"$keyword\")");

// sql语句:
SELECT * FROM 表名 WHERE name LIKE %$keyword%  ORDER BY  ORDER BY LENGTH(name) - LENGTH($keyword)

排序【按照位置】

// laravel用法：
$arrayCondition['orderByRaw'] = \DB::raw("case when name = \"$keyword\" then 1 
        when name like \"$keyword%\" then 2
        when name like \"%$keyword%\" then 3
        when name like \"%$keyword\" then 4
        else 0 end");
        
// sql语句:
SELECT * FROM 表名 WHERE name LIKE %$keyword%  ORDER BY 
case
 when name = $keyword then 1
 when name like '$keyword%' then 2
 when name like '%$keyword%' then 3
 when name like '%$keyword' then 4
 else 0
end