jsoncontains mysql,MySQL过滤器JSON_CONTAINS数组中的任何值

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')

");

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

解决方案

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')

)

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)

");