Laravel 查询 JSON 列中是否包含数组任意值的两种高效方案_技术教程

本文详解在 laravel 8 中通过 query builder 精准筛选 json 数组列（如 `location_ids`）包含指定整数数组中**至少一个值**的记录，提供兼容旧版 mysql 的 `wherejsoncontains` 循环方案和高性能的 `json_overlaps` 原生函数方案。

在 Laravel 应用中处理 JSON 类型字段（如 MySQL 的 JSON 列）时，常需实现「目标 JSON 数组是否与给定 PHP 数组存在交集」的逻辑。例如，用户拥有多个门店访问权限（$locations = [1, 3, 7]），而关联表 shop_access 的 location_ids 列存储形如 ["1", "2", "5"] 的字符串化整数数组——注意：数据库中为字符串，PHP 中为整数，需特别注意类型一致性。

✅ 方案一：兼容性广 —— whereJsonContains + 闭包 OR 条件

适用于所有支持 JSON_CONTAINS 的 MySQL 版本（5.7+），无需额外依赖。核心思路是将 $locations 中每个值单独构造 WHERE JSON_CONTAINS(location_ids, '"'.$value.'") 条件，并用 orWhere 组合：

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_CONTAINS 要求第二个参数为 JSON 字符串，故需转为 '"1"'
                    $sub->orWhereJsonContains('location_ids', (string)$location);
                }
            });
        });
    }

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

⚠️ 关键注意事项： whereJsonContains() 第二个参数必须是字符串形式的 JSON 值（如 "1"），而非纯整数 1。因此需强制转换 (string)$location；若 $locations 为空数组，循环不执行，条件恒假 → 建议前置校验：if ($locations) { ... }；大量 $locations 值会导致 SQL 中生成大量 OR 条件，可能影响查询性能与可读性。

✅ 方案二：高性能推荐 —— JSON_OVERLAPS 原生函数（MySQL 8.0.17+）

若项目使用 MySQL 8.0.17 或更高版本，JSON_OVERLAPS() 是更优雅、高效的替代方案：它直接判断两个 JSON 数组是否存在公共元素，且自动处理类型转换（["1"] 与 [1] 视为等价）。

public function scopeViewable($query)
{
    $user = Auth::user();
    $locations = $user->getShopAccess()
        ->pluck('id')
        ->values(); // 重置键名，确保生成标准 JSON 数组格式 [1,3,7]

    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->whereRaw('JSON_OVERLAPS(location_ids, ?)', [$locations->toJson()]);
    });
}

✅ 优势说明：

单条 SQL 条件，简洁清晰，避免 OR 链膨胀；

JSON_OVERLAPS 内部优化，性能优于多次 JSON_CONTAINS；

自动兼容整数/字符串类型（[1] 与 ["1"] 可匹配）；

->values() 确保 toJson() 输出无间隙索引数组（如 [1,3,7]），避免关联数组导致的 JSON 对象 {} 格式错误。

? 总结与选型建议

方案	兼容性	性能	代码简洁性	推荐场景
whereJsonContains + foreach	✅ MySQL 5.7+	⚠️ 数据量大时下降	❌ 较冗长	需兼容老版本 MySQL 的项目
JSON_OVERLAPS + whereRaw	✅ MySQL 8.0.17+	✅ 最优	✅ 极简	新项目或已升级 MySQL 的系统

无论选用哪种方式，请始终确保：

数据库列类型为 JSON（非 TEXT），以启用原生 JSON 函数；
在 shopAccess 关系定义中正确配置 casts（如 'location_ids' => 'array'）；
对高频查询的 location_ids 列建立 GENERATED COLUMN + INDEX（如 ALTER TABLE shop_access ADD location_ids_json JSON AS (location_ids); CREATE INDEX idx_location_overlap ON shop_access (location_ids_json);）以进一步加速。