如何更新大表中5万行数据？解决MySQL LIMIT子查询报错问题

问题背景 #

有一张超1000万行数据的大表customers_card_psp，需要更新满足以下条件的前50000行：

select *
from customers_card_psp
where status="not_added"
  and psp_id = 2
order by id
limit 50000

由于已在customers_card_psp(psp_id, status)上创建复合索引，上述查询执行速度极快（不到1秒）。但将其作为子查询用于UPDATE语句时：

update customers_card_psp
set status = "failure"
where id in (select id
             from customers_card_psp
             where status="not_added"
               and psp_id = 2
             order by id
             limit 50000)

系统抛出错误：

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

解决方案 #

方法1：直接在UPDATE中使用LIMIT（最优解） #

直接将筛选条件、排序和LIMIT整合到UPDATE语句中，逻辑和原SELECT查询完全一致，且能复用现有索引，执行效率和原查询相当：

update customers_card_psp
set status = "failure"
where status="not_added"
  and psp_id = 2
order by id
limit 50000;

方法2：用JOIN替代IN子查询 #

通过JOIN关联筛选出的目标ID集合，绕过MySQL对IN子查询+LIMIT的限制：

update customers_card_psp t1
join (
    select id
    from customers_card_psp
    where status="not_added"
      and psp_id = 2
    order by id
    limit 50000
) t2 on t1.id = t2.id
set t1.status = "failure";

方法3：借助临时表中转ID #

先将筛选出的目标ID存入临时表，再基于临时表执行更新，适合复杂场景下的分步操作：

-- 创建临时表存储目标ID
create temporary table temp_ids (id int primary key);

-- 插入筛选出的前50000个ID
insert into temp_ids
select id
from customers_card_psp
where status="not_added"
  and psp_id = 2
order by id
limit 50000;

-- 执行更新
update customers_card_psp
set status = "failure"
where id in (select id from temp_ids);

-- 临时表会在会话结束后自动清理，也可手动删除
drop temporary table temp_ids;

内容的提问来源于stack exchange，提问作者Martin AJ