MYSQL表数据复制(指定数量)
INSERT INTO sichuan_mobile_v2_202307 SELECT * FROM sichuan_mobile_v2 WHERE create_time >='2023-07-01 00:00:00' and create_time <='2023-07-31 23:59:59' LIMIT 100000
由于业务中不指定数量的话,如果数据太多容易写入失败,故添加LIMIT 限制。
JAVA代码实现分批复制数据
相关业务代码展示:
public boolean movemsg(String lasttable, String starttime, String endtime) {
// 2022年11月2日09:34:21,由于数据量已经400万了一次性写入失败,现优化:分批次写入。
long maxId = -1L;
// boolean run = true;
// while(run) {
String insertSql = " INSERT INTO " + lasttable + " SELECT * FROM sichuan_mobile_v2 WHERE create_time >='" + starttime + "' and create_time <='" + endtime + "'";
// 2.查询已经写入最大主键值
String maxIdSql = " SELECT * FROM "+lasttable+" ORDER BY id DESC LIMIT 1 ";
List<Map<String, Object>> list = dao.getSession().createSQLQuery("select xx.* from ( "+maxIdSql+" )xx ")
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
if(list.size() >0) {
if(list.get(0)!= null) {
maxId = Long.parseLong(list.get(0).get("id").toString());
}
}
// 1.用最大ID去主表查询是否还有数据
String countSql = " SELECT count(*) num FROM sichuan_mobile_v2 WHERE create_time >='" + starttime + "' and create_time <='" + endtime + "'"
+ " and id > "+ maxId;
String tempCount = dao.getSession().createSQLQuery("select xx.* from ( "+countSql+" )xx ").uniqueResult().toString();
long count = Long.parseLong(tempCount);
if(count > 0) {
if(maxId > -1) {
insertSql += " and id > " + maxId;
}
insertSql += " LIMIT 100000 "; // 10万条一批写入
try {
dao.insert(insertSql);
} catch (Exception e) {
logger.error(e.getMessage(), e);
// run = false;
return false;
}
}
// else {
// run = false;
// }
// }
return true;
}```