mysql建立存储过程按月创建分区表（日志服务器表）

时间：2024-03-16 14:56:31

mysql存储过程按月创建表分区
参考并学习如下链接：

https://blog.csdn.net/aofavx/article/details/50378360
https://blog.csdn.net/aofavx/article/details/50393281

表结构如下：
mysql建立存储过程按月创建分区表（日志服务器表）
mysql建立存储过程按月创建分区表（日志服务器表）

#–设置mysql自动允许定时任务

set global event_scheduler =1;

#–建日志表

CREATE TABLE `SystemEvents` (
  略...
  `ReceivedAT` datetime NOT NULL COMMENT '创建时间',
  ...略
) ENGINE=InnoDB CHARSET=utf8 COMMENT='日志存储';

#–分区存储过程

create PROCEDURE pro_sys_logByMonth(IN tableName VARCHAR(20),IN timeColName VARCHAR(20))
COMMENT '每月按时添加表分区的存储过程，由定时任务调用'
BEGIN
    DECLARE p_id int;
    DECLARE nextDate date;
    DECLARE lastDate LONG;
    #--获取表中的现有的分区数量数量
    SELECT COUNT(partition_name) into p_id FROM INFORMATION_SCHEMA.partitions 
                                        WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
    if p_id=0 then
        #--获取下个月第一天的时间值，根据此值设置时间分区
        SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH) INTO nextDate  from DUAL;
        set @v_add=CONCAT('ALTER table ',tableName,' PARTITION by range(TO_DAYS(',timeColName,'))
                                    (partition ',CONCAT('par',p_id),' values less than (TO_DAYS(\'',nextDate,'\')))');
    ELSE
        #--获取表中现有的最大的分区日期
        SELECT max(partition_description) des into lastDate from INFORMATION_SCHEMA.partitions 
                                        WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
        SELECT DATE_ADD(FROM_DAYS(lastDate),INTERVAL 1 MONTH) INTO nextDate from DUAL;
        set @v_add=CONCAT('alter table ',tableName,' add partition (partition ',CONCAT('par',p_id),
                                                    ' values less than (TO_DAYS(\'',nextDate,'\')))');
    END IF;
    PREPARE stmt from @v_add;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

#–每月创建一个分区的定时任务

 create event event_sysLog on SCHEDULE EVERY 1 MONTH STARTS CURRENT_TIMESTAMP  on COMPLETION PRESERVE
 ENABLE do call pro_sys_logByMonth('SystemEvents','ReceivedAT');

#–添加联合索引

alter table SystemEvents add PRIMARY KEY(id,ReceivedAT);
alter table SystemEvents change id id bigint(32) not null auto_increment;
alter table SystemEvents auto_increment=1;

#–添加索引

alter TABLE SystemEvents add index inx_syslog_ip(FromIP);

#–查看sql执行时，查询了那些分区，及使用的索引

EXPLAIN PARTITIONs select * from SystemEvents  where FromIP='xxx.xxx.xxx.xxx' and  date_formar(ReceivedAT(),CCYY-MM-DD) <'XXXX-XX-XX';

相关文章