- create table order_history
- (
- id bigint unsigned auto_increment primary key,
- order_id bigint unsigned not null,
- customer_id bigint unsigned not null,
- goods_id bigint unsigned not null,
- payment_id bigint unsigned not null,
- details text,
- comments text,
- order_date datetime not null,
- status tinyint
- );
- alter table order_history algorithm=inplace, lock=none, drop primary key, add primary key (id, order_date);
- -- algorithm=inplace, lock=none 是 RDS for MySQL 5.6 支持的 online DDL 特性。
- -- 如果要修改主键,删除主键和添加主键建议放在一条语句中,以便充分利用 5.6 版本的 Online DDL 特性。
- alter table order_history partition by range columns (order_date) (
- partition p1606 values less than ('2016-07-01'),
- partition p1607 values less than ('2016-08-01'),
- partition p1608 values less than ('2016-09-01'),
- partition p1609 values less than ('2016-10-01'),
- partition p1610 values less than ('2016-11-01'),
- partition p1611 values less than ('2016-12-01'),
- partition p1612 values less than ('2017-01-01'),
- partition p0 values less than maxvalue
- );
- -- 将数据按月份分区
- delimiter //
- drop procedure sp_order_his_rotate//
- create procedure sp_order_his_rotate ()
- begin
- declare todrop_par varchar(10) default null;
- declare toadd_par varchar(10) default null;
- declare toadd_day varchar(10) default null;
- declare last_par varchar(10) default null;
- declare new_par varchar(10) default null;
- declare v_sql varchar(200) default null;
- select date_format(date_sub(curdate(), interval 6 month), 'p%y%m') into todrop_par;
- select date_format(date_add(curdate(), interval 1 month), 'p%y%m') into toadd_par;
- select date_format(date_add(curdate()-day(curdate())+1,interval 2 month), '%Y-%m-%d') into toadd_day;
- select partition_name into last_par from information_schema.partitions
- where table_name = 'order_history'
- and table_schema = 'db_name'
- and partition_description != 'maxvalue'
- order by partition_description asc limit 1;
- if todrop_par = last_par then
- select concat('alter table order_history drop partition ',todrop_par,';') into v_sql;
- set @v_sql=v_sql;
- prepare stmt from @v_sql;
- execute stmt;
- deallocate prepare stmt;
- end if;
- select partition_name into new_par from information_schema.partitions
- where table_name = 'order_history'
- and table_schema = 'db_name'
- and partition_description != 'maxvalue'
- order by partition_description desc limit 1;
- if toadd_par != new_par then
- select concat('alter table order_history reorganize partition p0 into (partition ', toadd_par, ' values less than (''', toadd_day,'''), partition p0 values less than (maxvalue));') into v_sql;
- set @v_sql=v_sql;
- prepare stmt from @v_sql;
- execute stmt;
- deallocate prepare stmt;
- end if;
- end;
- //
- delimiter ;
- drop event if exists order_his_rotate;
- delimiter //
- create event order_his_rotate
- on schedule every 1 month starts '2016-12-28 02:00:00'
- on completion preserve enable do
- begin
- call sp_order_his_rotate();
- end;
- //
- delimiter ;
来源: