本文介绍在 laravel 8 中如何对关联表的 json 数组列（如 `location_ids`）进行查询，精准筛选出至少包含 `$locations` 数组中任一值的记录，并提供兼容性兼顾的两种实现方案。
在 Laravel 应用中，当使用 MySQL 存储 JSON 格式的数组（例如 ["1", "2", "5"]）并需基于用户权限动态过滤数据时，常见的需求是：判断 JSON 列是否与给定 PHP 数组存在交集（即“至少包含一个值”）。由于 JSON 列中存储的是字符串型数字（如 "1"），而 $locations 是整型数组（如 [1, 3, 7]），直接比对需注意类型一致性。
✅ 推荐方案一：JSON_OVERLAPS()（MySQL 8.0.17+，推荐优先使用）
JSON_OVERLAPS() 是 MySQL 原生函数，语义清晰、性能优异，且天然支持类型自动转换（可安全匹配整数与字符串数字）：
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->whereHas('shopAccess', function (Builder $q) use ($locations) {
$q->where(function ($sub) use ($locations) {
foreach ($locations as $location) {
$sub->orWhereJsonContains('location_ids', (string) $location); // 显式转为字符串以匹配 JSON 中的 "1"
}
});
});
}⚠️ 注意：JSON_OVERLAPS() 要求 MySQL ≥ 8.0.17；若部署环境版本较低，请降级使用方案二。
✅ 方案二：循环 whereJsonContains()（全版本兼容）
适用于所有支持 JSON_CONTAINS 的 MySQL 版本（5.7+）。关键点在于：将整型 ID 显式转为字符串，以匹配 JSON 中的 "1" 而非 1：
$q->where(function ($sub) use ($locations) {
foreach ($locations as $location) {
$sub->orWhereJsonContains('location_ids', (string) $location);
}
});该写法生成类似以下 SQL：
WHERE JSON_CONTAINS(location_ids, '"1"') OR JSON_CONTAINS(location_ids, '"3"') OR JSON_CONTAINS(location_ids, '"7"')
? 补充说明与最佳实践
- 避免 N+1 问题：确保 shopAccess 关系已正确定义（如 belongsToMany(Shop::class)），并在调用处预加载（with('shopAccess')）以提升性能。
-
空数组保护：若 $locations 可能为空，建议前置校验：
if ($locations->isEmpty()) { return $query->whereRaw('0'); // 返回空结果集 } - 索引优化：MySQL 对 JSON_CONTAINS 和 JSON_OVERLAPS 的查询可利用生成列 + 普通索引加速（需额外建模，此处略）。
综上，优先采用 JSON_OVERLAPS() 提升可读性与执行效率；兼容性要求高时，使用带类型转换的 whereJsonContains() 循环方案，二者均能准确满足“JSON 数组列至少包含一个指定值”的业务场景。
