本文详解如何在 laravel 8 中使用 query builder 对 json 数组列（如 `location_ids`）进行高效过滤，支持“至少匹配一个”语义，并提供 `wherejsoncontains` 循环方案与 mysql 8.0.17+ 推荐的 `json_overlaps` 原生函数两种实现方式。
在 Laravel 应用中，当关联表的 JSON 列（例如 location_ids）存储字符串化整数数组（如 ["1", "2", "5"]），而我们需要筛选出该 JSON 数组中至少包含 $locations（整型数组，如 [1, 3, 7]）中任一元素的记录时，不能直接传入数组调用 whereJsonContains —— 因为该方法默认将整个参数作为单个值进行子串/元素匹配，而非集合交集判断。
✅ 推荐方案一：使用 JSON_OVERLAPS（MySQL ≥ 8.0.17）
若数据库版本支持（推荐生产环境优先采用），JSON_OVERLAPS 是最简洁、高效且语义清晰的原生方案。它直接判断两个 JSON 文档是否存在公共元素（自动处理类型转换与嵌套数组）：
public function scopeViewable($query)
{
$user = Auth::user();
$locations = $user->getShopAccess()->pluck('id')->values(); // 确保索引连续，避免 toJson() 产生关联数组
if ($user->hasPermissionTo('users.index')) {
return $query->whereHas('shopAccess', function (Builder $q) use ($locations) {
$q->whereRaw('JSON_OVERLAPS(location_ids, ?)', [$locations->toJson()]);
});
}
return $query->whereNull('id'); // 或其他默认限制逻辑
}⚠️ 注意事项：
- pluck('id')->values() 确保生成标准数值索引数组（如 [0 => 1, 1 => 3]），否则 toJson() 可能输出 {"0":"1","1":"3"}（对象格式），导致 JSON_OVERLAPS 匹配失败；
- location_ids 列值必须为合法 JSON 数组（如 ["1","2","5"]），且数据库字符集需为 utf8mb4；
- 此方法在 MySQL 层完成计算，性能远优于 PHP 循环 + 多次 OR，且可利用生成列 + 索引进一步优化（如添加 JSON_CONTAINS 虚拟列索引）。
✅ 方案二：兼容低版本 MySQL 的 whereJsonContains 循环
若数据库版本低于 8.0.17，可退而求其次，对 $locations 数组逐项调用 whereJsonContains 并组合为 OR 条件：
public function scopeViewable($query)
{
$user = Auth::user();
$locations = $user->getShopAccess()->pluck('id')->toArray();
if ($user->hasPermissionTo('users.index')) {
return $query->whereHas('shopAccess', function (Builder $q) use ($locations) {
$q->where(function ($sub) use ($locations) {
foreach ($locations as $location) {
// 将整数转为字符串，匹配 JSON 中的 "1"、"2" 等
$sub->orWhereJsonContains('location_ids', (string) $location);
}
});
});
}
return $query->whereNull('id');
}? 关键细节：
- whereJsonContains('location_ids', '1') 能正确匹配 ["1","2","5"]，但不能匹配 ["10","11"]（精确元素匹配，非子串）；
- 必须将整型 $location 显式转为字符串 (string) $location，否则 Laravel 可能传递整数 1，而 JSON 中存储的是字符串 "1"，导致匹配失败；
- 当 $locations 较大时（如 > 100 项），生成的 SQL OR 链过长可能影响查询计划，建议配合缓存或预聚合优化。
? 总结与选型建议
| 方案 | 适用场景 | 性能 | 可维护性 | 备注 |
|---|---|---|---|---|
| JSON_OVERLAPS | MySQL ≥ 8.0.17 | ⭐⭐⭐⭐⭐ | ⭐⭐⭐⭐⭐ | 推荐首选；一行代码，语义明确，数据库原生优化 |
| orWhereJsonContains 循环 | 兼容旧版 MySQL | ⭐⭐☆ | ⭐⭐⭐ | 注意字符串转换与数组结构；适合中小规模数据 |
无论采用哪种方式，请务必在 shop_access 表的 location_ids 列上建立生成列（Generated Column）并添加索引以提升查询性能，例如：
ALTER TABLE shop_access ADD COLUMN location_ids_json JSON AS (CAST(location_ids AS JSON)) STORED, ADD INDEX idx_location_overlap (location_ids_json);
然后在查询中改用 JSON_OVERLAPS(location_ids_json, ?) 进一步加速。
