mysql中用逗号隔开的某字段，如何判断其他表的字段值是否在这个字段中

因为要增加需求，需要将线上表中老数据，修改为新数据的规则。
线上两张表，sequence_number中is_use有3作废、2到期状态，需要根据这个状态和school_ai_authorization中的is_deleted修改新增的state字段。
sequence_number表结构：蓝色为重要查询字段。
school_ai_authorization表：

当然，细心的你看到了sequence_number_id，为什么没有用这个，因为数据库没有这个字段的数据。尴尬吧！

错误的查询方式

select saa.* from la_school_ai_authorize saa where saa.is_deleted = 1
and  saa.textbook_level_id in
(select textbook_level_id from la_sequence_number where is_use = 2)
and saa.school_id in
(select school_id from la_sequence_number where is_use = 2)
and saa.telephone in
(select telephone from la_sequence_number where is_use = 2)
1
2
3
4
5
6
7

in（1,2）in（3,4）in（5,6）因为三个in的意思是只要在后边的数据中就行，不是数据过滤。

正确用in

select saa.* from la_school_ai_authorize saa where saa.is_deleted = 1
and  (saa.textbook_level_id,saa.school_id,saa.telephone) in
(select textbook_level_id,school_id,telephone from la_sequence_number where is_use = 3)
1
2
3

这才是是根据sequence_number中到期数据，查询school_ai_authorize中被删除的数据是因为序列号到期还是作废。

还有个更恶心的东西，sequence_number中textbook_level_id并不只在这个字段中存在，还有个“，”拼接到textbook_level_ids字段中，所以这个也要查询出来。这里用到了find_in_set(字段，textbook_level_ids) > 0 如果存在，返回在textbook_level_ids中的位置。

SELECT saa.*
FROM la_school_ai_authorize saa
WHERE saa.is_deleted = 1 and 
EXISTS (
    SELECT 1 FROM la_sequence_number sn
    WHERE sn.is_use = 2 and (FIND_IN_SET(saa.textbook_level_id, sn.textbook_level_ids) > 0 or saa.textbook_level_id = sn.textbook_level_id)
		and sn.school_id = saa.school_id and sn.telephone = saa.telephone
);
1
2
3
4
5
6
7
8

但是find_in_set查询效率比较慢，量大查询很长时间，所以可以用关联查询优化子查询。或者优化表结构ids就不该存在。或者加索引。

#用多表联查优化子查询。
SELECT DISTINCT saa.*
FROM la_school_ai_authorize saa INNER JOIN la_sequence_number sn
on sn.school_id = saa.school_id and sn.telephone = saa.telephone and sn.is_use = 2 and saa.is_deleted = 1 and (FIND_IN_SET(saa.textbook_level_id, sn.textbook_level_ids) > 0 or saa.textbook_level_id = sn.textbook_level_id)
1
2
3
4

记得用distinct不然数据会多的。

根据条件修改表

#is_use = 3 作废 state = 2
update la_school_ai_authorize_copy1 saa set saa.state = 2, saa.is_deleted = 0
WHERE saa.is_deleted = 1 and 
EXISTS (
    SELECT 1 FROM la_sequence_number sn
    WHERE sn.is_use = 3 and (FIND_IN_SET(saa.textbook_level_id, sn.textbook_level_ids) > 0 or saa.textbook_level_id = sn.textbook_level_id)
		and sn.school_id = saa.school_id
);
#is_use = 2 已到期 state = 3
update la_school_ai_authorize_copy1 saa set saa.state = 3, saa.is_deleted = 0
WHERE saa.is_deleted = 1 and 
EXISTS (
    SELECT 1 FROM la_sequence_number sn
    WHERE sn.is_use = 2 and (FIND_IN_SET(saa.textbook_level_id, sn.textbook_level_ids) > 0 or saa.textbook_level_id = sn.textbook_level_id)
		and sn.school_id = saa.school_id and sn.telephone = saa.telephone
);
# 剩余是解绑的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17