赞
踩
- 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%'; -- 查看定时器状态
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。