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 分表,拆分策略都有哪些?各在什么情况下应用?