Laravel 8 Eloquent实现MySQL商品搜索优先级排序及分页优化

针对电商场景下的搜索排序需求，我们可以通过单查询条件排序实现优先级控制，避免多次查询关联表带来的性能损耗，同时支持分页。

核心思路 #

通过MySQL的CASE语句为不同匹配规则的结果分配权重，按权重降序排列，实现以下优先级：

1. 商品名称以搜索词开头（如Chicken freezed）
2. 商品名称包含独立的搜索词（如Fried chicken leg，搜索词作为完整单词存在）
3. 商品名称任意位置包含搜索词（其他含chicken的条目）

具体实现代码 #

假设搜索词为$search = 'chicken'，结合Laravel Eloquent的关联查询与分页：

$search = 'chicken';

$goods = Good::with(['promo', 'categories']) // 预加载关联表，避免N+1查询
    ->where('name', 'like', "%{$search}%") // 先筛选所有含搜索词的商品
    ->orderByRaw("
        CASE
            WHEN name LIKE ? THEN 3
            WHEN name REGEXP ? THEN 2
            ELSE 1
        END DESC,
        name ASC
    ", ["{$search}%", "[[:<:]]{$search}[[:>:]]"]) // 参数绑定防止SQL注入
    ->paginate(15); // 分页，可调整每页数量

代码说明 #

• 条件筛选：先用where过滤出所有包含搜索词的商品，减少后续排序的数据量
• 权重排序：
  • 匹配name LIKE 'chicken%'的条目权重设为3，优先级最高
  • 用MySQL正则表达式[[:<:]]和[[:>:]]匹配独立单词（搜索词前后为单词边界），权重设为2
  • 剩余匹配条目权重设为1，最后按权重降序排列，相同权重的商品按名称升序
• 参数绑定：避免直接拼接字符串导致SQL注入风险

性能优化建议 #

如果商品数量较大（数千条以上），建议添加索引提升查询效率：

1. 前缀索引：针对"以搜索词开头"的场景，给name字段添加前缀索引：

   ALTER TABLE goods ADD INDEX idx_goods_name_prefix (name(50));
   

2. 全文索引：若MySQL版本支持，添加全文索引优化单词匹配的性能：

   ALTER TABLE goods ADD FULLTEXT INDEX ft_goods_name (name);
   

   优化后的查询代码（替换正则表达式为全文检索）：

   $goods = Good::with(['promo', 'categories'])
       ->whereRaw("MATCH(name) AGAINST(? IN BOOLEAN MODE)", [$search])
       ->orderByRaw("
           CASE
               WHEN name LIKE ? THEN 3
               WHEN MATCH(name) AGAINST(? IN BOOLEAN MODE) THEN 2
               ELSE 1
           END DESC,
           name ASC
       ", ["{$search}%", "+{$search}"])
       ->paginate(15);
   

   全文检索的+{$search}表示必须包含该单词，性能远高于正则表达式匹配。

方案优势 #

• 仅需一次查询，避免多次查询关联表带来的额外开销
• 数据库层面完成排序，效率远高于PHP内存中处理结果
• 完美兼容Laravel的分页功能，无需额外处理分页逻辑

内容的提问来源于stack exchange，提问作者Bedivierre