欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-03-26 15:17:43
...
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