MySQL将非分区表转化为分区表

1、备份数据到alarminfo_old
insert into alarminfo_old(deviceid,type,status,message,alarmtime,planid,alertnum,confirm_userid,confirm_username,confirm_useralias,confirm_time,confirm_message) 
select deviceid,type,status,message,alarmtime,planid,alertnum,confirm_userid,confirm_username,confirm_useralias,confirm_time,confirm_message from alarminfo;
2、删除alarminfo
drop table alarminfo;
3、创建带有分区的alarminfo
CREATE TABLE `alarminfo` (
`id`  bigint(20) NOT NULL AUTO_INCREMENT,
`deviceid`  varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`type`  tinyint(4) NOT NULL DEFAULT 0 ,
`status`  tinyint(4) NULL DEFAULT 0 COMMENT '报警状态，1产生；2消失' ,
`message`  varchar(1024) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '处理意见' ,
`alarmtime`  bigint(20) NULL DEFAULT 0 ,
`planid`  int(11) NULL DEFAULT 0 ,
`alertnum`  tinyint(4) NULL DEFAULT 0 COMMENT '报警通道号' ,
`confirm_userid`  int(11) NULL DEFAULT 0 ,
`confirm_username`  varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`confirm_useralias`  varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`confirm_time`  bigint(20) NULL DEFAULT NULL ,
`confirm_message`  varchar(1024) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`, `type`)
) PARTITION BY LIST(type) (
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2),
PARTITION p3 VALUES IN (6,100,101)
);
4、拷贝数据
create procedure cp_data()
begin
declare i int; 
set i=0;
   while i<60 do
     insert into alarminfo(deviceid,type,status,message,alarmtime,planid,alertnum,confirm_userid,confirm_username,confirm_useralias,confirm_time,confirm_message) select deviceid,type,status,message,alarmtime,planid,alertnum,confirm_userid,confirm_username,confirm_useralias,confirm_time,confirm_message from alarminfo_old where id >= i*10000 and  id <(i+1)*10000;
     set i=i+1;
     select sleep(2);
     end while;
end
5、删除备份表
drop table alarminfo_old;

 

 

mysql 分表，拆分策略都有哪些？各在什么情况下应用？

 

http://www.dewen.org/q/696