check set of ids in mysql set of ids (each item of firs set, must be in second set)-CodePudding

 ----------- --------- 
| InvoiceId | trueIDs |
 ----------- --------- 
| ab12345   | 1,2,35  |
| cd1234567 | 92,28,1 |
| asdf12345 | 351,8,1 |
 ----------- ---------

UserAllowed (925,28,1,99,1059,314,422,96,917,356)

Is there any way to compare two sets of id's? I want to choose best performance way, to compare between two sets of variable , and if one item of first set exist in table, then return InvoiceId...

There is an array in php, and a comma separated string in db table cell.

If of the array values (UserAllowed), exist in database (trueIDs) return InvoiceId.

May be the php array is large, and it isn't logical to execute the query for each php array item.

SELECT
    Invoices.InvoiceId
FROM
    Invoices 
WHERE
(925,28,1,99,1059,314,422,96,917,356) ???? Invoices.trueIDs

CodePudding user response：

SELECT Invoices.InvoiceId
FROM Invoices 
WHERE JSON_OVERLAPS(CONCAT('[', @UserAllowed, ']'), CONCAT('[', trueIDs, ']'));

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=62fae329e23d3f2885c6b16ce1770765