MySQL中分表的创建及按月分表的存过

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号数据库就会自动创建一张子表，并确保将当月的数据插入到当月的表里。