作者官方网站:http://www.wxl568.cn
MYsql分区使用粘贴使用注意代码之间的空格
CREATE TABLE `wxl_ad_visit_record` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`invalid_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`supplier_id` VARCHAR(50) NOT NULL,
`ad_id` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`ID`,invalid_date)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(to_days(invalid_date))
(PARTITION p20190326 VALUES LESS THAN (to_days('2019-03-26')),PARTITION p20190327 VALUES LESS THAN (to_days('2019-03-27')),PARTITION p20190328 VALUES LESS THAN (to_days('2019-03-28')));
访问日志表,每天自动增加分区
DELIMITER $$
CREATE EVENT wxl_ad_visit_record_auto_partition
ON SCHEDULE
EVERY 1 day
STARTS str_to_date('2019-03-25 00:00:00','%Y-%m-%d %T')
COMMENT '访问日志表,每天自动增加分区'
DO
BEGIN
set @c_table='wxl_ad_visit_record';
set @c_day=DATE_SUB(curdate(),INTERVAL -1 DAY);
set @p_c_day=DATE_FORMAT(NOW(),'%Y%m%d');
set @p_name=concat('p_',@c_table,@p_c_day);
set @p_sql=concat('ALTER TABLE ',@c_table,' ADD PARTITION (PARTITION ',@p_name,' VALUES LESS THAN (TO_DAYS ("',@c_day,'")))');
select @p_sql;
PREPARE auto_create_partion from @p_sql;
execute auto_create_partion;
END $$
delimiter ;