mysql单次批量大数据的删除和插入

Question

需求：

问题描述

数据库MySQL，引擎innodb，当前表1数据接近2000万条
需要写定时任务，每天运行一次，也可以运行多次，将表1中最远一天的数据迁移至另外的表2
大概每天产生的数据有将近10万条
总结下来：每天需要从表1中删除将近10万条数据，然后把这10万条数据插入到表2，并且要保证从表1中删除和向表2中插入是原子的，不能出现表1中删除成功，但是表2中插入失败，或者表1中删除失败，但是表2中插入成功

哪位老哥有好的主意，望指点一下小弟，太迷茫了~

想到的解决方法

由于项目是golang写的，用的xorm框架，我目前想到的方法是：
1，select id from t2 order by id desc limit 1，获取到表2中最新的数据的主键id，保存为max_id
2，select * from t1 where id > max_id order by id asc limit 1000，从表1中查询到最远的1000条数据，保存在slice中。由于是使用的xorm框架，所以直接在方法中传入一个slice，slice中保存这1000条数据。
3，开启事务，遍历slice，读取id，根据id从表1中删除，并在表2插入

这么做有什么风险吗？

Answer 1

如果你中间没有什么数据需要处理的话，直接用sql语句来搬移应该是效率最高的

INSERT INTO xxx SELECT * FROM yyy WHERE zzz;
DELETE FROM yyy WHERE zzz;

一定要在事务中执行。

Answer 2

应该改用 先插入,后删除 方案，即使失败了回滚，还可以手动处理。 但是仍然有几点要注意：

• 不要一次性把10w条数据写到slice中，而是改用一次5000这种，拆分成几个小任务，除非你不在乎内存，并且将mysql的 max_allowed_packet参数调大
• 不要遍历slice的插入，效率极低，一秒的插入量不会上百，如果你不在乎时间也没关系
• 需要开启transaction

以下是伪代码，将1000条数据一次性迁移, 封装成一个函数即可。

 var data []models.Record    
 db.Query(`select id from t1 order  limit 1000`).Find(&data)
 
 // 使用bufferString，拼接sql插入语句
 
 sqlBuffer := bytes.NewBufferString("insert into  tabelName values") 
 for index,v := range data {
    if index == len(data)-1 {
        sqlBuffer.WriteString("(value1, value2),") 
    } else {
        sqlBuffer.WriteString("(value1,value2)") 
    }
    // 省略部分代码，注意当index为最后一条的时候，最后的逗号不要添加
 }
    
// 
tx := db.NewSession()
// 执行sqlBuffer的结果
tx.Close()