mysql 按年分区

您所在的位置：网站首页 › mysql按日期创建分区表 › mysql 按年分区

mysql 按年分区

2024-07-15 15:19| 来源: 网络整理| 查看: 265

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

【本文地址】

mysql 按年分区

mysql 按年分区

今日新闻

推荐新闻