0
+-----------+---------+
| 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
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
shay
  • 3
  • 2
  • 3
    Just for future reference [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – RiggsFolly Jun 21 '22 at 09:27
  • 1
    You _could_ create a `FIND_IN_SET()` dynamically for each entry in your PHP array, and join them all with OR ... But of course you rather shouldn't, and instead properly normalize your data model. – CBroe Jun 21 '22 at 09:32
  • This would be a lot easier if the database wasn't denormalised- see link posted by RiggsFolly above. Then it could be done with an IN clause in the query – ADyson Jun 21 '22 at 09:32
  • What is the largest id? If it is not "too big" consider using bits in a `BLOB`. Then a simple `&` for testing. (Probably needs MySQL 8.0 to handle more than 64 bits.) – Rick James Jun 23 '22 at 04:49

1 Answers1

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

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

Akina
  • 39,301
  • 5
  • 14
  • 25