mysql透過計劃任務建立月表

lovefoolself發表於2024-11-27
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
  1. 建立計劃任務,從某個時間點一個月建立一次
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分庫分表。

相關文章