'Search for a multiple values in a multi_value column in MYSQL

I have a table in which there is a column that stores id(s) of another table, there could be a single id or multiple ids separated by a comma (,).

I want to search multiples values against the column: "s" which again can have multiple values. So searching: "5, 3, 1" should match with the column "id" 2, 4, 5, 6 in the below table

Similarly, search "4, 5" should match with the column "id" 6, 7, 8.

So how can I get these rows distinctively?

| id | s    |
+----+------+
|  1 | 2    |
|  2 | 1    |
|  3 | 2    |
|  4 | 2,3  |
|  5 | 1,3  |
|  6 | 1,2,5|
|  7 | 4,5  |
|  8 | 3,4  |
+----+------+

Please help!

mysqlsql


Solution 1:[1]

SELECT *
FROM test 
WHERE JSON_OVERLAPS(CONCAT('[', test.s, ']'), @criteria_as_json)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8f5043a3734d9b7ded81597a26b09ae7

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Akina

Related Questions