如何对数据库存储的整数数组字段做包含查询筛选关联模型数据

你当前使用的where('colors','like', "%2%")写法存在逻辑漏洞：如果数组中存在包含2的多位数（如12、23），也会被误命中，无法精准匹配单个整数值。结合你的存储场景，有两种可直接落地的实现方案：

方案1：MySQL 5.7+/MariaDB 10.2+版本（推荐） #

你存储的数组为标准JSON字符串格式，可直接使用数据库自带的JSON_CONTAINS函数实现精准匹配：

// 要查询的目标整数
$targetNum = 2;
$products = Product::whereHas('options', function (\Illuminate\Database\Eloquent\Builder $query) use ($targetNum) {
    $query->whereRaw('JSON_CONTAINS(colors, ?)', [(string)$targetNum]);
    // 如需同时匹配sizes字段包含目标值，取消注释下一行
    // $query->orWhereRaw('JSON_CONTAINS(sizes, ?)', [(string)$targetNum]);
})->get();

配套优化建议 #

• 将options表的colors、sizes字段从varchar类型改为JSON类型，数据库会自动校验格式，查询效率更高
• 在Option模型中添加字段类型转换，后续读写时会自动完成数组和JSON字符串的互转：

// app/Models/Option.php
protected $casts = [
    'colors' => 'array',
    'sizes' => 'array',
    'price' => 'float'
];

方案2：低版本数据库不支持JSON函数 #

如果数据库版本过低无法使用JSON函数，可使用多条件模糊匹配规避误判问题：

$targetNum = 2;
$products = Product::whereHas('options', function (\Illuminate\Database\Eloquent\Builder $query) use ($targetNum) {
    $query->where(function ($q) use ($targetNum) {
        // 匹配目标值在数组开头
        $q->where('colors', 'like', "[{$targetNum},%")
          // 匹配目标值在数组中间
          ->orWhere('colors', 'like', "%,{$targetNum},%")
          // 匹配目标值在数组末尾
          ->orWhere('colors', 'like', "%,{$targetNum}]")
          // 匹配数组只有目标值一个元素
          ->orWhere('colors', '=', "[{$targetNum}]");
    });
})->get();

该写法覆盖了数组中目标值的所有可能位置，不会出现多位数误匹配的问题。

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