使用mysql存储过程加定时任务（job）实现把表的数据按月分类创建历史表并把原表数据转移到历史表中。
注：如果执行报语法错误，创建存储过程时和结束时，输入分隔符
DELIMITER$$
......
$$
DELIMITER;
DELIMITER
-- 如果存储过程已经存在，先删除
DROP PROCEDURE IF EXISTS 'back_history';
-- 创建存储过程
CREATE PROCEDURE back_history()
BEGIN
-- 现在是几年几月
SET @dateStr = DATE_FORMAT(NOW(),'%Y%m');
-- 30天前是哪一天(年月日)
SET @preDateStr = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d');
-- 30天前是哪一天(年月)
SET @preMonthStr = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m');
-- 30天之前的月份的上一个月
SET @ppreMonthStr = DATE_FORMAT(DATE_SUB(DATE_SUB(CURDATE(),INTERVAL 30 DAY),INTERVAL 1 MONTH),'%Y%m');
#上个月的年月
SET @lastDateStr = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m');
-- 定义建表sql
SET @createStr = CONCAT("CREATE TABLE dialog_result_detail_history" ,@preMonthStr,
"(`owner_id` varchar(32) NOT NULL DEFAULT '业户编号必须唯一',
`owner_name` varchar(100) DEFAULT NULL COMMENT '业户名称',
`business_id` varchar(100) DEFAULT NULL COMMENT '经营许可证编号',
`owner_tel` varchar(30) DEFAULT NULL COMMENT '联系电话',
`address` varchar(200) DEFAULT NULL COMMENT '所在地址',
`create_time` datetime DEFAULT NULL COMMENT '记录日期',
PRIMARY KEY (`owner_id`))");
-- 查询30天前的那天的数据
SET @total = (SELECT count(*)from result where DATE_FORMAT(create_date,'%Y-%m-%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y-%m-%d'));
-- 定义一次批量插入的数据量
SET @everyNum = 30000;
-- 开启事务
START TRANSACTION;
-- 判断CONCAT('dialog_result_detail_history',@preMonthStr)表是否存在
if (SELECT COUNT(table_name) FROM information_schema.TABLES WHERE table_name=CONCAT('dialog_result_detail_history',@preMonthStr) )>0 THEN
-- 表已经存在，查询30天之前的那天的数据，往表中插入
-- 记录剩余数据量
SET @remainTotal = @total;
-- 如果数据量大于@everyNum，循环分批插入
IF @total>@@everyNum THEN
SET @temp=0;
SET @temp1 = @everyNum;
loop_insertLoop: LOOP
SET @tableName = CONCAT("dialog_result_detail_history_",@preMonthStr);
SET @insertStr = CONCAT("insert into ",@tableName,
"(owner_id,owner_name,business_id,owner_tel,address,create_time)
(select owner_id,owner_name,business_id,owner_tel,address,create_time
from result where DATE_FORMAT(create_date,'%Y%m%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d')ORDER BY
owner_id DESC LIMIT ", @temp,",",@temp1," )" );
-- 预编译sql
PREPARE stmt FROM @insertStr;
-- 执行sql
EXECUTE stmt;
-- 计算还有多少数据量需要插入表中
SET @remainTotal = @remainTotal-@temp1;
IF @remainTotal>@everyNum THEN
SET @temp = @temp+@temp1;
ELSE
SET @temp = @temp+@temp1;
-- 剩余数据量小于@everyNum，一次执行，并推出循环
SET @tableName = CONCAT("dialog_result_detail_history_",@preMonthStr);
SET @insertStr = CONCAT("insert into ",@tableName,
"(owner_id,owner_name,business_id,owner_tel,address,create_time)
(select owner_id,owner_name,business_id,owner_tel,address,create_time
from result where DATE_FORMAT(create_date,'%Y%m%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d')ORDER BY
owner_id DESC LIMIT ", @temp,",",@remainTotal," )" );
PREPARE stmt FROM @insertStr;
EXECUTE stmt;
LEAVE loop_insertLoop;
END IF;
END LOOP;
ELSE
-- 总数据量小于@everyNum
-- 定义插入表语句
SET @tableName = CONCAT("dialog_result_detail_history_",@preMonthStr);
SET @insertStr = CONCAT("insert into ",@tableName,
"(owner_id,owner_name,business_id,owner_tel,address,create_time)
(select owner_id,owner_name,business_id,owner_tel,address,create_time
from result where DATE_FORMAT(create_date,'%Y%m%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d'))");
PREPARE stmt FROM @insertStr;
EXECUTE stmt;
END IF;
-- 删除原表数据
SET @deleteSql = "DELETE FROM result where owner_id IN (SELECT owner_id FROM (SELECT owner_id FROM result WHERE DATE_FORMAT(create_date,'%Y%m%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d'))t1 ) ";
PREPARE stmt FROM @deleteSql
EXECUTE stmt;
else
-- 表不存在，先判断30天之前的那个月的上一个月的表是否存在，如果存在表示已经初始化了，30天之前刚好是那月的1号，30天前的那个月的表还未创建
IF (SELECT COUNT(table_name) FROM information_schema.TABLES where table_name=CONCAT('dialog_result_detail_history_',@ppreMonthStr))>0 THEN
-- 创建表
SET @ownerInfo = CONCAT(@createStr,"ENGINE=InnoDB DEFAULT CHARSET=utf8;");
PREPARE stmt1 FROM @ownerInfo;
EXECUTE stmt1;
-- 重复上面的if里面的分批循环插入数据
ELSE
-- 30天之前的那个月的上一个月的表也不存在
-- 说明程序是第一次启动，需要初始化
-- 上个月的日期
SET @preMonthStr = DATE_SUB(CURDATE(),INTERVAL 30 DAY);
-- 今年年份
SET @dateStr = DATE_FORMAT(NOW(),'%Y');
SET @i = 0;
loop_label: LOOP
-- 日期表名
SET @loop_table = DATE_FORMAT(@preMonthStr,'%Y%m');
-- 创建表并复制30天前的那个月的数据
SET @stmt1 = CONCAT("create table ","dialog_result_detail_history_",@loop_table,"(SELECT * FROM result where DATE_FORMAT(create_date,'%Y%m')=",@loop_table," AND DATE_FORMAT(create_date,'%Y%m%d')<=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d') );");
PREPARE stmt1 FROM @stmt1;
EXECUTE stmt1;
-- 这样创建并复制数据的表没有主键，需要设置主键
-- 设置表的主键
SET @stmt2 = CONCAT("ALTER TABLE dialog_result_detail_history_",@loop_table," ADD PRIMARY KEY(`owner_id`)");
-- 删除原表数据
SET @deleteSql = CONCAT("DELETE FROM result where owner_id IN (SELECT owner_id FROM (SELECT owner_id FROM result where DATE_FORMAT(create_date,'%Y%m')=",@loop_table," AND DATE_FORMAT(create_date,'%Y%m%d')<=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d') )t1);");
PREPARE delsql FROM @delSql;
EXECUTE delsql;
-- 查询现在执行的月份的上个月
SET @preMonthStr = DATE_SUB(@preMonthStr,INTERVAL 1 MONTH);
-- 如果循环到今年1月，结束循环
IF @loop_table = CONCAT(@dateStr,'01') THEN
LEAVE loop_label;
END IF;
END LOOP loop_label;
END IF;
END IF;
-- 提交事务
COMMIT;
END
DELIMITER;
