MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id1 INT, id2 INT, id3 INT, id4 INT, id5 INT, id6 INT);
INSERT INTO test VALUES
(9, 2, 5, 3, 8, 4),
(6, 1, 8, 2, 7, 3),
(2, 3, 6, 1, 9, 5),
(9, 7, 8, 3, 2, 6),
(1, 2, 7, 4, 8, 5);
SELECT * FROM test;

Records: 5  Duplicates: 0  Warnings: 0




id1
id2
id3
id4
id5
id6


9
2
5
3
8
4

6
1
8
2
7
3

2
3
6
1
9
5

9
7
8
3
2
6

1
2
7
4
8
5


SET @criteria = '2,4,8';
WITH cte AS ( SELECT *, (FIND_IN_SET(id1, @criteria) > 0) + 
                        (FIND_IN_SET(id2, @criteria) > 0) + 
                        (FIND_IN_SET(id3, @criteria) > 0) + 
                        (FIND_IN_SET(id4, @criteria) > 0) + 
                        (FIND_IN_SET(id5, @criteria) > 0) + 
                        (FIND_IN_SET(id6, @criteria) > 0) matches 
               FROM test )
SELECT *
FROM cte
WHERE matches = ( SELECT MAX(matches)
                  FROM cte );


id1
id2
id3
id4
id5
id6
matches


9
2
5
3
8
4
3

1
2
7
4
8
5
3