mysql分区:每天自动添加新分区

test数据库中position表按日期(天)分区:
需要做:

  • 对已有数据分区
  • 添加过程存储(相当于函数)
  • 添加事件(相当于定时调用函数)

  1. 开启事件调度器(默认关闭)

    SET GLOBAL event_scheduler = ON; 
    
  2. 必须对已有数据先进行分区

    ALTER TABLE position PARTITION BY RANGE(TO_DAYS(date))
    (
        PARTITION p20181028 VALUES LESS THAN (TO_DAYS('2018-10-29')),
        PARTITION p20181029 VALUES LESS THAN (TO_DAYS('2018-10-30')),
        PARTITION p20181030 VALUES LESS THAN (TO_DAYS('2018-10-31'))
    )
    
  3. 分区脚本

    use test;
    
    DELIMITER ||
    -- 删除存储过程
    drop procedure if exists auto_set_partitions ||
    -- 注意:使用该存储过程必须保证相应数据库表中至少有一个手动分区
    -- 创建存储过程[通过数据库名和对应表名]-建多少个分区,分区时间间隔为多少
    -- databasename:创建分区的数据库
    -- tablename:创建分区的表的名称
    -- partition_number:一次创建多少个分区
    -- partitiontype:分区类型[0按天分区,1按月分区,2按年分区]
    -- gaps:分区间隔,如果分区类型为0则表示每个分区的间隔为 gaps天;
    --       如果分区类型为1则表示每个分区的间隔为 gaps月
    --            如果分区类型为2则表示每个分区的间隔为 gaps年
    create procedure auto_set_partitions (in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, in partition_number int, in partitiontype int, in gaps int)
    L_END:
    begin     
        declare max_partition_description varchar(255) default '';
        declare p_name varchar(255) default 0;       
        declare p_description varchar(255) default 0;   
        declare isexist_partition varchar(255) default 0; 
     declare i int default 1;
      
     -- 查看对应数据库对应表是否已经有手动分区[自动分区前提是必须有手动分区]
        select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename  and table_name = tablename limit 1;
        -- 如果不存在则打印错误并退出存储过程
        if isexist_partition <=> "" then
           select "partition table not is exist" as "ERROR";
           leave L_END;
        end if;
     
        -- 获取最大[降序获取]的分区描述[值]
        select partition_description into max_partition_description  from information_schema.partitions where table_schema = databasename  and table_name = tablename order by partition_description desc limit 1;
       
        -- 如果最大分区没有,说明没有手动分区,则无法创建自动分区
        if max_partition_description <=> "" then
           select "partition table is error" as "ERROR";
           leave L_END;
        end if;
     
        -- 替换前后的单引号[''两个引号表示一个单引号的转义]
        -- set max_partition_description = REPLACE(max_partition_description, '''', '');
         -- 或使用如下语句
         set max_partition_description = REPLACE(max_partition_description-1, '\'', '');
     
       -- 自动创建number个分区
        while (i <= partition_number) do
                     if (partitiontype = 0) then
                         -- 每个分区按天递增,递增gaps天
                         set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day); 
                     elseif (partitiontype = 1) then
                         -- 每个分区按月递增,递增gaps月
                         set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month); 
                     else 
                         -- 每个分区按年递增,递增gaps年
                         set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps year);
                     end if;
                     -- 删除空格
                     set p_name = REPLACE(p_description, ' ', '');
                     -- 例如10.20的记录实际是less than 10.21
                     set p_description = DATE_ADD(p_description, interval 1 day); 
                     -- 如果有横杆替换为空
              set p_name = REPLACE(p_name, '-', '');
                     -- 删除时间冒号
                     set p_name = REPLACE(p_name, ':', '');
                     -- alter table tablename add partition ( partition pname values less than ('2017-02-20 10:05:56') );
              set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
                     -- set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
                     -- 打印sql变量
              -- select @sql;
                     -- 准备sql语句
              PREPARE stmt from @sql;
                     -- 执行sql语句
              EXECUTE stmt;
                     -- 释放资源
              DEALLOCATE PREPARE stmt;
                     -- 递增变量
              set i = (i + 1) ;
     
        end while;          
    end ||
    -- 恢复语句中断符
    DELIMITER ;
    
    
  4. 添加事件处理,每天执行一次

    DELIMITER ||
    drop event if exists auto_set_partitions  ||
    create event auto_set_partitions 
    on schedule every 1 day
    starts '2018-10-30 23:59:59'
    do
    BEGIN
        call auto_set_partitions('test', 'position', 1, 0, 1);
    END ||
    DELIMITER ;
    

ps: 其他操作

  • 删除表中分区
alter table table_name drop partition p0;
  • 修改事件
ALTER EVENT
event_name

ON SCHEDULE schedule
[RENAME TO new_event_name][ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE][COMMENT 'comment']
DO sql_statement
  • 删除事件
DROP EVENT [IF EXISTS] auto_set_partitions;

但当一个事件正在运行中时,删除该事件不会导致事件停止,事件会执行到完毕为止

  • 查看事件是否开启
show variables like 'event_scheduler';
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 214,658评论 6 496
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 91,482评论 3 389
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 160,213评论 0 350
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,395评论 1 288
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,487评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,523评论 1 293
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,525评论 3 414
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,300评论 0 270
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,753评论 1 307
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,048评论 2 330
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,223评论 1 343
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,905评论 5 338
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,541评论 3 322
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,168评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,417评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,094评论 2 365
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,088评论 2 352

推荐阅读更多精彩内容