mysql和csv效率,如何在mysql中将值与csv值进行比较？

Table 1

id(int) | name(varchar)

1 | at,bat

2 | cat,at,bat,mat

3 | mat,cat

4 | sat,bat

Table 2

id(int) | type(varchar)

1 | at

2 | mat

As you can see table1 contains csv strings. Now I need to fetch the ids from Table 1 whose names exist in Table2 type field.

Is there any pure mysql query way of doing this? if not, what would be the most time efficient way of doing this in case of large record sets?

解决方案select distinct t1.id

from table1 t1

join table2 t2 on find_in_set(t2.type, t1.name) > 0