连接时匹配MySQL中逗号分隔的值[重复]

Question

我有几个表我需要加入，我不能改变数据库，因为它正在多个不同的应用程序中使用。

第一个表如下所示

id, list_of_foreign_id


1, "1, 3, 4, 8, 12, 13, 14, 15, 16, 23"

2, "1, 3, 4, 8, 23"

3, "1, 3, 8, 12, 13, 14, 15, 23"

4, "7"

....

ID, name

1, "john Oliver"

2, "John Doe"

3, "Arthur Dent"

....

Select ...
from
A
join
B on a.list_of_foreign_id contains B.id

Answer 1

可以使用find_in_set()。

SELECT *
       FROM A
            INNER JOIN B
                       ON find_in_set(B.id, A.list_of_foreign_id);