MySQL过滤器JSON_CONTAINS数组中的任何值 [英] MySQL Filter JSON_CONTAINS Any value from Array

问题描述

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

I have a JSON field in a MySQL database that contains values like [1,3,4,7]. I would like to be able to easily supply another array from a PHP variable and determine if there is any overlap. I know this example does not work, but this is what I am trying to do:

$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，如何获取该查询以返回结果?

How can I get this query to return a result since there is a 1 in each array?

推荐答案

您可以为每个单独的值执行JSON_CONTAINS:

You could perform a JSON_CONTAINS for each separate value:

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

当要搜索的值存储在PHP变量中时，您可以像上面那样构建上述SQL:

When the values to be searched for are stored in a PHP variable, then you could build the above SQL like this:

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

这篇关于MySQL过滤器JSON_CONTAINS数组中的任何值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！