MySQL根据CreateDate自动创建分区

关于MySQL表分区的方法就不再赘述，补充一下

Ps：

1、MySQL中Rang 必须以DateTime格式，Timestamp不支持

2、DateTime格式5.6以上可以支持默认值CURRENT_TIMESTAMP

存储过程：

DELIMITER $$

DROP PROCEDURE IF EXISTS `ebaydata`.`pro_partition`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_partition`(IN tableName VARCHAR(20),IN timeColName VARCHAR(20) )
BEGIN
    DECLARE p_id int;
    DECLARE p_des date;
    DECLARE lasttime VARCHAR(20);
    DECLARE nexttime varchar(20);
    -- 获取表中的现有的分区数量数量
    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_FORMAT(DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH),'%Y-%m-%d') INTO p_des from DUAL;
        -- 创建第一个表分区的动态sql
        set @v_add=CONCAT('ALTER table ',tableName,' PARTITION by range COLUMNS(',timeColName,')
         (partition ',CONCAT('par',p_id),' values less than (\'',p_des,'\'))');
    ELSE
        -- 获取表中现有的最大的分区日期
        SELECT max(partition_description) des into lasttime from INFORMATION_SCHEMA.partitions 
        WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
        -- lasttime的值是'2015-12-01' 因是含引号格式的字符串，直接用str_to_date函数和 date_format函数处理不成功。必须通过动态sql把它转换成没有引号的时间字符串
	set @v_add_a=CONCAT('select adddate(str_to_date(',lasttime,',\'%Y-%c-%d\'),Interval 1 month) into @nexttime from dual ');
        PREPARE stm from @v_add_a;
        EXECUTE stm;
        DEALLOCATE PREPARE stm;
        -- 将编译执行的stm结果存储到nexttime中
        set nexttime=@nexttime; 
        -- 添加表分区的sql
        set @v_add=CONCAT('alter table ',tableName,' add partition (partition ',CONCAT('par',p_id),' values less than (\'',nexttime,'\'))');
    END IF;
    PREPARE stmt from @v_add;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END$$

DELIMITER ;

创建Event：

DELIMITER $$

ALTER DEFINER=`root`@`localhost` EVENT `event_partition_listing_offer_bymonth` ON 
SCHEDULE EVERY 1 MONTH 
STARTS '2017-01-20 15:33:58' ON 
COMPLETION PRESERVE 
ENABLE 

DO call  pro_partition('t_listing_offer','create_date')$$

DELIMITER ;

表：

CREATE TABLE `t_listing_offer` (                                                     
                   `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',                    
                   `item_id` varchar(32) NOT NULL COMMENT 'ItemID',                                   
                   `user_id` varchar(255) DEFAULT NULL COMMENT 'eBay买家',                          
                   `variation` varchar(2000) DEFAULT NULL COMMENT 'Variation',                        
                   `price` double(10,2) DEFAULT NULL COMMENT '价格',                                
                   `quantity` int(11) DEFAULT NULL COMMENT '数量',                                  
                   `purchase_date` varchar(32) DEFAULT NULL COMMENT '购买日期',                   
                   `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',  
                   PRIMARY KEY (`id`,`create_date`)                                                   
                 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8