最近用MySQL做了一个每月1号00:00:00创建一张表并将上个月的表数据导入到新创建的表中,在此做个小记。
1. 要做这个之前先要:
-- 设置好时区
set time_zone = '+8:00';
-- 开启事件调度器
set GLOBAL event_scheduler = 1;
-- 选择数据库
use test;
2. 先创建一个存储过程或者函数:
-- 创建owner_info_month表的存储过程
CREATE PROCEDURE create_owner_info_table_every_month()
BEGIN
SET @dateStr = DATE_FORMAT(NOW(), '%Y_%m');
SET @nowDateStr = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),'%Y_%m');
SET @createStr = CONCAT("CREATE TABLE owner_info_" ,@dateStr,
"(`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`),
UNIQUE KEY ");
SET @keyStr = CONCAT(" PK_GOV_OWNER_INFO_" ,@dateStr,"(`owner_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
SET @OwnerInfo = concat(@createStr ,@keyStr);
PREPARE stmt FROM @OwnerInfo;
EXECUTE stmt;
-- 将上个月的表数据导入到新创建的表中;
SET @tableName = CONCAT("owner_info_" ,@dateStr);
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 owner_info_" ,@nowDateStr);
PREPARE stmt FROM @InsertStr;
EXECUTE stmt;
END ;
创建的第二个存储过程:
-- 创建vehicle_info_month表的存储过程
CREATE PROCEDURE create_vehicle_info_table_every_month ()
BEGIN
SET @dateStr = DATE_FORMAT(NOW(), '%Y_%m');
SET @nowDateStr = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),'%Y_%m');
SET @createStr = CONCAT("CREATE TABLE vehicle_info_",@dateStr,
"(`vehicle_id` varchar(50) NOT NULL COMMENT '车辆唯一标识',
`vehicle_name` varchar(30) NOT NULL COMMENT '车牌号',
`color` smallint(5) NOT NULL DEFAULT '2' COMMENT '车牌颜色',
`owner_id` varchar(32) DEFAULT NULL,
`vehicle_type` varchar(10) DEFAULT NULL COMMENT '车辆类型类别编码',
`create_time` datetime DEFAULT NULL COMMENT '记录日期',
PRIMARY KEY (`vehicle_id`),");
SET @keyStr1 = concat(" KEY idx_gv_oid_",@dateStr,"(`owner_id`),");
SET @keyStr2 = concat(" KEY idx_gv_trans_type_",@dateStr,"(`vehicle_type`),");
SET @keyStr3 = concat(" KEY idx_gv_trans_vname_",@dateStr,"(`vehicle_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
SET @VehicleInfo = concat(@createStr ,@keyStr1 ,@keyStr2 ,@keyStr3);
PREPARE stmt FROM @VehicleInfo;
EXECUTE stmt;
-- 将上个月的表数据导入到新创建的表中;
SET @tableName = CONCAT("vehicle_info_" ,@dateStr);
SET @InsertStr = CONCAT("insert into ",
@tableName,
"(vehicle_id, vehicle_name, color, owner_id,vehicle_type,create_time)
select vehicle_id, vehicle_name, color, owner_id,vehicle_type,create_time
from vehicle_info_" , @nowDateStr);
PREPARE stmt FROM @InsertStr;
EXECUTE stmt;
END;
3. 创建定时任务或者叫调度器事件:
-- 创建定时任务
-- 以$$作为分隔符
DELIMITER $$
SET GLOBAL event_scheduler = 1;
-- 事件名
CREATE EVENT event_create_table_every_month
-- 间隔一个月
ON SCHEDULE EVERY 1 MONTH
-- 第一次执行时间
STARTS date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day), interval 1 month),interval 0 hour)
ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
-- 要调用的函数或者存储过程
CALL create_owner_info_table_every_month();
CALL create_vehicle_info_table_every_month();
END $$
-- 将分隔符重置为;
DELIMITER ;
若有更好的方式,或者文中有错误,欢迎评论留言!