最近需要更新数据表,但是使用in条件有3w左右的数据,一开始直接使用where条件进行更新,等待3分钟之后超时
UPDATE a_device set use_status = 3 WHERE use_status = 4 AND Serial not in (SELECT `sn` FROM sn );
于是修改成如下,ps:项目目前再测试阶段,所以可以这么更新数据
UPDATE a_device set use_status = 3 WHERE use_status = 4;
UPDATE a_device set use_status = 4 WHERE Serial in (SELECT `sn` FROM sn );
但是还是超时,最终方案,只能先把sn搜索出来当做条件,避免连续搜索占用资源。
UPDATE a_device set use_status = 3 WHERE use_status = 4;
SELECT
CONCAT("’", IF(`sn` IS NULL, '', `sn`), "’") AS `sn`
FROM sn
UPDATE a_device set use_status = 4 WHERE Serial in (上一步搜索的结果删除最后一个逗号)
完成数据修改