最近用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
若有更好的方式,或者文中有错误,欢迎评论留言!