mysql 按年分区

迷南。 2022-05-04 03:56 283阅读 0赞

CREATE TABLE `sunmnet_visit_record` (
    `id`  bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键ID' ,
    `ip`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ip地址' ,
    `browser`  varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '浏览器' ,
    `kernel_version`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '内核版本' ,
    `name`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '网站名称' ,
    `address`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '网站地址' ,
    `duration`  int(11) NULL DEFAULT NULL COMMENT '响应时长（毫秒）' ,
    `status`  int(1) NOT NULL DEFAULT 0 COMMENT '响应状态（0：正常 1：超时）' ,
    `create_date`  datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间(抓取时间)' ,
    `update_date`  datetime NULL COMMENT '修改时间' ,
    PRIMARY KEY (`id`, `create_date`)
    )PARTITION BY RANGE (YEAR(create_date))(
    	PARTITION p2018 VALUES LESS THAN (2019),
    	PARTITION p2019 VALUES LESS THAN (2020)
    );

通过sql新增数据，之后查看数据保存在对应的分区条数

SELECT
    	partition_name,
    	partition_expression,
    	partition_description,
    	table_rows
    FROM
    	information_schema. PARTITIONS
    WHERE
    	table_schema = SCHEMA ()
    AND table_name = 'sunmnet_visit_record'

给表添加新的分区

ALTER TABLE sunmnet_visit_record ADD PARTITION(PARTITION p2020 VALUES LESS THAN (2021));

删除表分区

ALTER TABLE sunmnet_visit_record DROP PARTITION p2019

写个存储过程，自动添加分区

drop  PROCEDURE `add_partition_by_year`;
    CREATE PROCEDURE `add_partition_by_year` ()
    	BEGIN
    		
    		DECLARE next_year int;
    		DECLARE cur_name VARCHAR(10);
    		DECLARE exist_procedure_name int;
    		DECLARE limit_year int;
    		SET next_year = YEAR(CURRENT_DATE())+1;
    		SET limit_year = next_year + 1;
    		SET cur_name = 'p'+ next_year;
    		
    		SELECT
    			count(*) INTO exist_procedure_name
    		FROM
    			information_schema.PARTITIONS
    		WHERE
    			table_schema = SCHEMA ()
    		AND table_name = 'sunmnet_visit_record'
    		AND PARTITION_NAME = cur_name;
    		
    		IF exist_procedure_name = 0 THEN
    			SET @addSql=CONCAT('ALTER TABLE sunmnet_visit_record ADD PARTITION (PARTITION ',cur_name,' VALUES LESS THAN (',limit_year,'));');
    			SELECT @addSql;
    			PREPARE stmt FROM@addSql;
    			EXECUTE stmt;
    		END IF;
    	COMMIT;
    END;

定时器事件创建，一个月调用一次

CREATE EVENT
    IF NOT EXISTS addPartitionJob ON SCHEDULE EVERY 1 MONTH ON COMPLETION PRESERVE DO
    	CALL add_partition_by_year();

至此所有的准备工作已经写完了，做完这些，mysql要想利用定时器必须的做准备工作，就是把mysql的定时器给开启了：  
SET GLOBAL event\_scheduler = 1;  -- 启动定时器  
SET GLOBAL event\_scheduler = 0;  -- 停止定时器  
  
  
紧接着还要开启事件：  
ALTER EVENT eventJob ON  COMPLETION PRESERVE ENABLE;   -- 开启事件  
ALTER EVENT eventJob ON  COMPLETION PRESERVE DISABLE;  -- 关闭事件  
  
SHOW VARIABLES LIKE '%sche%'; -- 查看定时器状态