對test數(shù)據(jù)庫中position表按日期（天）分區(qū)：
需要做：

• 對已有數(shù)據(jù)分區(qū)
• 添加過程存儲（相當于函數(shù)）
• 添加事件（相當于定時調(diào)用函數(shù)）

1. 開啟事件調(diào)度器（默認關(guān)閉）

   SET GLOBAL event_scheduler = ON; 
   

2. 必須對已有數(shù)據(jù)先進行分區(qū)

   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. 分區(qū)腳本

   use test;
   
   DELIMITER ||
   -- 刪除存儲過程
   drop procedure if exists auto_set_partitions ||
   -- 注意：使用該存儲過程必須保證相應(yīng)數(shù)據(jù)庫表中至少有一個手動分區(qū)
   -- 創(chuàng)建存儲過程[通過數(shù)據(jù)庫名和對應(yīng)表名]-建多少個分區(qū)殉挽，分區(qū)時間間隔為多少
   -- databasename：創(chuàng)建分區(qū)的數(shù)據(jù)庫
   -- tablename：創(chuàng)建分區(qū)的表的名稱
   -- partition_number：一次創(chuàng)建多少個分區(qū)
   -- partitiontype：分區(qū)類型[0按天分區(qū)丰涉，1按月分區(qū)，2按年分區(qū)]
   -- gaps：分區(qū)間隔斯碌，如果分區(qū)類型為0則表示每個分區(qū)的間隔為 gaps天一死；
   --       如果分區(qū)類型為1則表示每個分區(qū)的間隔為 gaps月
   --            如果分區(qū)類型為2則表示每個分區(qū)的間隔為 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;
     
    -- 查看對應(yīng)數(shù)據(jù)庫對應(yīng)表是否已經(jīng)有手動分區(qū)[自動分區(qū)前提是必須有手動分區(qū)]
       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;
    
       -- 獲取最大[降序獲取]的分區(qū)描述[值]
       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;
      
       -- 如果最大分區(qū)沒有,說明沒有手動分區(qū),則無法創(chuàng)建自動分區(qū)
       if max_partition_description <=> "" then
          select "partition table is error" as "ERROR";
          leave L_END;
       end if;
    
       -- 替換前后的單引號[''兩個引號表示一個單引號的轉(zhuǎn)義]
       -- set max_partition_description = REPLACE(max_partition_description, '''', '');
        -- 或使用如下語句
        set max_partition_description = REPLACE(max_partition_description-1, '\'', '');
    
      -- 自動創(chuàng)建number個分區(qū)
       while (i <= partition_number) do
                    if (partitiontype = 0) then
                        -- 每個分區(qū)按天遞增,遞增gaps天
                        set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day); 
                    elseif (partitiontype = 1) then
                        -- 每個分區(qū)按月遞增,遞增gaps月
                        set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month); 
                    else 
                        -- 每個分區(qū)按年遞增,遞增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;
                    -- 執(zhí)行sql語句
             EXECUTE stmt;
                    -- 釋放資源
             DEALLOCATE PREPARE stmt;
                    -- 遞增變量
             set i = (i + 1) ;
    
       end while;          
   end ||
   -- 恢復語句中斷符
   DELIMITER ;
   
   

4. 添加事件處理，每天執(zhí)行一次

   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: 其他操作

• 刪除表中分區(qū)

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;

但當一個事件正在運行中時傻唾，刪除該事件不會導致事件停止投慈，事件會執(zhí)行到完畢為止

• 查看事件是否開啟

show variables like 'event_scheduler';