mysql通过计划任务创建月表

一、背景

系统中存在一个大数据量的表，为了性能，打算一个月创建一个月表来分表

二、步骤

1. 首选，开启mysql的事务调度器
   SET GLOBAL event_scheduler = ON;
2. 编写创建表的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `create_table`()
BEGIN
    DECLARE year INT;
    DECLARE month INT;
    DECLARE table_name VARCHAR(255);
		DECLARE index_name VARCHAR(255);

    SET year = YEAR(CURDATE());
    SET month = MONTH(CURDATE());
    SET table_name = CONCAT('test_', year, '_', LPAD(month,2,'0'));
		SET index_name = CONCAT('plm_event_time_',year, '_', LPAD(month,2,'0'));

    SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ', table_name, ' (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT \'测试\',
  `etime` datetime NULL DEFAULT NULL COMMENT \'测试\',
  `etype` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT \'测试\',
  `ecomment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT \'测试\',
  `tagcode` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT \'测试\',
	  `tagtype` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT \'测试，1:测试 2:测试 3:测试\',
  `pno` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT \'测试\',
  `pcode` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT \'测试、测试、测试\',
  `pname` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT \'测试、测试、测试\',
  `zcode` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT \'测试\',
  `zname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT \'测试\',
  `isdelete` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT \'测试\',
  `createtime` datetime NULL DEFAULT NULL COMMENT \'测试\',
  `updatetime` datetime NULL DEFAULT NULL COMMENT \'测试\',
  `deletetime` datetime NULL DEFAULT NULL COMMENT \'测试\',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX ',index_name,'(`etime`) USING BTREE',
') ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = \'测试\' ROW_FORMAT = Dynamic;');
   


		PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
		
END

3. 创建计划任务,从某个时间点一个月创建一次

CREATE DEFINER = `root`@`%` EVENT `test`.`task`
ON SCHEDULE
EVERY '1' MONTH STARTS '2023-01-01 00:00:00'
DO BEGIN
        CALL create_table();
    END;

三、后记

这种方式适合小型项目，如果是真的数据量庞大建议用mycat分库分表。