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';

作者：cooooper
链接：https://www.jianshu.com/p/52f83d55eae5
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。