php - MySQL 过滤器 JSON_CONTAINS 数组中的任何值

Question

我在 MySQL 数据库中有一个 JSON 字段，其中包含类似 [1,3,4,7] 的值。我希望能够轻松地从 PHP 变量提供另一个数组并确定是否存在任何重叠。我知道这个例子不起作用，但这是我想要做的:

$DaysVar = $_GET['Days']; --Example is [1,5,8]

$sql = mysqli_query($db, "
    SELECT ScheduleID, 
           Days --Example is [1,3,4,7]
    FROM Schedule
    WHERE JSON_CONTAINS(Days, '$DaysVar')
");

由于每个数组中都有一个 1，我怎样才能让这个查询返回一个结果？

Answer 1

在 MySQL 5.7.8+ 上，您可以执行 JSON_CONTAINS对于每个单独的值:

SELECT * 
FROM   Schedule 
WHERE  (   JSON_CONTAINS(Days, '1')
        OR JSON_CONTAINS(Days, '2')
        OR JSON_CONTAINS(Days, '6')
       )

当要搜索的值存储在 PHP 变量中时(如您的问题)，您可以像这样构建上面的 SQL:

$DaysVar = $_GET['Days'];
$condition = implode(" OR ", array_map(function($day) {
    return "JSON_CONTAINS(Days, '".intval($day)."')";
}, $DaysVar));
$sql = mysqli_query($db, "
    SELECT ScheduleID, 
           Days
    FROM   Schedule
    WHERE  ($condition)
");

为避免 SQL 注入(inject)，您应该更好地准备创建字符串的语句，例如使用 substr(str_repeat("OR JSON_CONTAINS(Days, ?)", count($DaysVar)), 3) 和然后在其上调用 mysqli_prepare，然后绑定(bind)，...等等。

MySQL 8.*

从 MySQL 8 开始，您可以使用 JSON_OVERLAPS :

SELECT * 
FROM   Schedule
WHERE  JSON_OVERLAPS(Days, '$DaysVar')

同样，您最好准备语句(使用 ? 占位符)，然后将 $DaysVar 绑定(bind)到它，最后执行它。