mysql 按年分区

最新推荐文章于 2025-04-18 09:14:16 发布

an74520

最新推荐文章于 2025-04-18 09:14:16 发布

阅读量5.1k

点赞数 1

分类专栏： mysql sql语句文章标签： mysql分区

本文链接：https://blog.csdn.net/an74520/article/details/83273101

版权

mysql 同时被 2 个专栏收录

3 篇文章

订阅专栏

sql语句

3 篇文章

订阅专栏

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%'; -- 查看定时器状态