1、首先创建子表:
create table tb_information_coll_202001(
Id BIGINT (20) PRIMARY KEY auto_increment,
str1 VARCHAR(100),
str2 VARCHAR(100),
ipAddr VARCHAR(100),
info VARCHAR(100),
time_coll datetime
)ENGINE=MyISAM DEFAULT CHARSET=utf8 auto_increment=1;
2、创建主表并关联子表:
create table information_coll(
Id BIGINT(20) PRIMARY KEY auto_increment,
str1 VARCHAR(100),
str2 VARCHAR(100),
ipAddr VARCHAR(100),
info VARCHAR(100),
time_coll datetime
)ENGINE=MERGE UNION=(tb_information_coll_202001)INSERT_METHOD=LAST CHARSET=utf8 auto_increment=1;
3、编写创建分表的存过:
delimiter // -- 设定“//”为结束符
CREATE PROCEDURE P_ceatetable(var VARCHAR(30))
BEGIN
#DECLARE tbName VARCHAR(20);
-- 查询主表需要关联的子表
SET @tbName=(select table_name from information_schema.tables where table_schema='mes'AND table_name LIKE "tb_information_coll_%" ORDER BY table_name DESC LIMIT 0,1);
-- 创建以“年月”为后缀的表(例:tb_information_coll_202001)
SET @pddl = concat('CREATE TABLE ', var,'_', DATE_FORMAT(NOW(), '%Y%m'), ' LIKE ',@tbName );
PREPARE stmt FROM @pddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 更改主表(这里更改的是主表所关联的子表)
SET @pdd2 = concat('ALTER TABLE ',var,' UNION=(',@tbName,',',var,'_', DATE_FORMAT(NOW(), '%Y%m'),')INSERT_METHOD=LAST');
PREPARE stmt1 FROM @pdd2;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END //
delimiter;
4、设置定时任务,确保每月1号都能准时创建一张子表:
create event createtable_event
on schedule every 1 MONTH STARTS '2020-03-01 00:00:01' -- 设置时间间隔为1个月,开始时间为“2020-03-01 00:00:01”
on completion preserve disable -- 表示创建后并不开始生效
do call P_createtable("tb_information_coll"); -- 该事件要执行的操作
alter event copytable_event on completion preserve enable; -- 将该事件设置为开启状态
这样,每月的1号数据库就会自动创建一张子表,并确保将当月的数据插入到当月的表里。