CREATE TABLE alarm_copy1 LIKE alarm; CREATE TABLE alarm_detail_copy1 LIKE alarm_detail;
新建alarm_2表与明细表alarm_2_detail
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
CREATE TABLE `alarm_2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `alarmId` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '告警Id', `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `alarmId`(`alarmId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '主表' ROW_FORMAT = Dynamic;
CREATE TABLE `alarm_2_detail` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `iotId` bigint(20) NOT NULL, `paramValue` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `iotId`(`iotId`) USING BTREE, CONSTRAINT `fk_iotId` FOREIGN KEY (`iotId`) REFERENCES `alarm_2` (`id`) ON DELETE CASCADE ) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '明细表' ROW_FORMAT = Compact;
获取原表主键最大id
1
SELECT @maxId:=MAX(id) FROM alarm;
导出出A类型主数据，再导入新表
FIELDS TERMINATED BY ','表示字段间用,分割
OPTIONALLY ENCLOSED BY '"'表示所有字符用"包裹
ESCAPED BY '*'表示用*代表转义字符
LINES TERMINATED BY '\r\n'表示每条数据用空格加回车结束
1 2 3
SELECT * FROM alarm WHERE id<@maxId AND type='A' INTO OUTFILE 'alarm_2.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '*' LINES TERMINATED BY '\r\n'; #导入 LOAD DATA INFILE 'alarm_2.txt' INTO TABLE alarm_2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '*' LINES TERMINATED BY '\r\n';
导出出A类型明细数据，再导入新表
1 2
SELECT ad.* FROM alarm_detail ad WHERE EXISTS (SELECT iotId FROM alarm a WHERE ad.iotId=a.id) INTO OUTFILE 'alarm_2_detail.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '*' LINES TERMINATED BY '\r\n'; LOAD DATA INFILE 'alarm_2_detail.txt' INTO TABLE alarm_2_detail FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '*' LINES TERMINATED BY '\r\n';
导出出B类型主数据，再导入copy表
1 2
SELECT * FROM alarm WHERE id<@maxId AND type='A' INTO OUTFILE 'alarm_copy1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '*' LINES TERMINATED BY '\r\n'; LOAD DATA INFILE 'alarm_copy1.txt' INTO TABLE alarm_copy1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '*' LINES TERMINATED BY '\r\n';
导出出B类型明细数据，再导入新表
1 2
SELECT ad.* FROM alarm_detail ad WHERE EXISTS (SELECT iotId FROM alarm_copy1 ac WHERE ac.iotId=ad.id) INTO OUTFILE 'alarm_detail_copy1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '*' LINES TERMINATED BY '\r\n'; LOAD DATA INFILE 'alarm_detail_copy1.txt' INTO TABLE alarm_detail_copy1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '*' LINES TERMINATED BY '\r\n';
二、项目暂停、更新代码
项目需要暂停几分钟，修改表名并更新代码
修改表名称
将*_copy1表名与原表互换
1 2 3 4 5 6
ALTER TABLE alarm RENAME TO alarm_temp; ALTER TABLE alarm_detail RENAME TO alarm_detail_temp; ALTER TABLE alarm_copy1 RENAME TO alarm; ALTER TABLE alarm_detail_copy1 RENAME TO alarm_detail; ALTER TABLE alarm_temp RENAME TO alarm_copy1; ALTER TABLE alarm_detail_temp RENAME TO alarm_detail_copy1;
修改自增数
为了保证在新数据插入新表后不会与暂未拆分数据主键冲突，需要将所有表的自增值改为原表自增数大小，
1 2 3 4 5 6 7 8 9 10 11
#创建存储过程修改自增值 DROP PROCEDURE IF EXISTS copy_auto_increment; CREATE PROCEDURE `copy_auto_increment`(IN `sourceTable` varchar(50),IN `targetTable` varchar(50)) BEGIN DECLARE num BIGINT; DECLARE alterSql VARCHAR(250); SELECT AUTO_INCREMENT INTO num FROM information_schema.tables WHERE table_name = sourceTable AND table_schema = DATABASE(); SET num=num+100; SET alterSql=CONCAT('ALTER TABLE ',targetTable,' AUTO_INCREMENT=',num); EXECUTE alterSql; END;
DROP PROCEDURE IF EXISTS post_alarm_split; CREATE PROCEDURE `post_alarm_split`(IN `maxId` bigint) BEGIN -- 定义接收游标数据的变量 DECLARE done BOOLEAN DEFAULT 0; DECLARE var_id BIGINT DEFAULT 0; DECLARE var_alarmId VARCHAR(50); DECLARE var_type VARCHAR(50); DECLARE cur_alarm CURSOR FOR (SELECT id,alarmId,type FROM iot_alarm_copy1 WHERE id>=maxId ORDER BY id); #退出标志 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ; OPEN cur_alarm; REPEAT FETCH cur_alarm INTO var_id,var_alarmId,var_type; IF NOT done THEN #A类 IF var_type='A' THEN INSERT INTO alarm_2 VALUES(var_id,var_alarmId,var_type); INSERT INTO alarm_2_detail (SELECT id,iotId,paramValue FROM alarm_detail_copy1 WHERE iotId = var_id); #B类 ELSE INSERT INTO alarm VALUES(var_id,var_alarmId,var_type); INSERT INTO alarm_detail (SELECT id,iotId,paramValue FROM alarm_detail_copy1 WHERE iotId = var_id); END IF; END IF; UNTIL done END REPEAT; CLOSE cur_alarm; END;