mysql中使用in有可能导致sql长度超过限制,导致执行失败,这时候我们可以使用exits来简化sql
例如:
update eas_student t1 set register_status = 2 where t1.del_flag = 0 and t1.id in (
select distinct t2.student_id from eas_student_plan t2 where t2.del_flag = 0
);
如果子查询中的数量特别多,就会导致整个sql长度过长。
可以这样修改:
update eas_student t1 set t1.register_status = 2 where t1.del_flag = 0 and exists(
select * from eas_student_plan t2 where t2.student_id = t1.id and t2.del_flag = 0
);
注意子查询中通过t2.student_id = t1.id 来关联