MySql 定时任务和存储过程,每月创建一张表

最近用MySQL做了一个每月1号00:00:00创建一张表并将上个月的表数据导入到新创建的表中,在此做个小记。

1. 要做这个之前先要:

    -- 设置好时区
    set time_zone = '+8:00';
    -- 开启事件调度器
    set GLOBAL event_scheduler = 1;
    -- 选择数据库
    use test;
   
   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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 ;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43

创建的第二个存储过程:

-- 创建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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44

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 ;
 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

若有更好的方式,或者文中有错误,欢迎评论留言!

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值