时间:2021-07-01 10:21:17 帮助过:40人阅读
无详细内容 无 --备份数据库--mysqldump -h localhost -uroot -p123456 database dump.sql--初始化interfaceType--先处理掉select it_id ,count(*) as sum from server_interfaces group by it_id having sum1drop table interfaces_type;create table interf
<无详细内容> <无>--备份数据库 --mysqldump -h localhost -uroot -p123456 database > dump.sql --初始化interfaceType --先处理掉 select it_id ,count(*) as sum from server_interfaces group by it_id having sum>1 drop table interfaces_type; create table interfaces_type ( id int(5) NOT NULL AUTO_INCREMENT primary key comment '主键,作为接口id的前缀', type_name varchar(20) not null comment '接口类型名称', max_it_id int(11) comment '接口类型的接口id最大值' ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; insert into interfaces_type(type_name) select distinct it_type from interfaces; update interfaces_type set max_it_id=id*10000 ; drop PROCEDURE resetInterfaceType; delimiter // CREATE PROCEDURE resetInterfaceType() BEGIN DECLARE minId INT; DECLARE maxId INT; SELECT max(id) into maxId from interfaces_type ; update interfaces_type set id=id+maxId; SELECT min(id) into minId from interfaces_type ; update interfaces_type set id=id-minId+1; update interfaces_type set max_it_id=id*10000; END// delimiter ; call resetInterfaceType(); drop PROCEDURE resetInterface; delimiter // CREATE PROCEDURE resetInterface() BEGIN declare itType varchar(20) ; declare beginId int(11); declare itCount int(11); declare itId int(11); declare isFinished boolean default false; declare maxItId int(11); declare maxItId2 int(11); DECLARE ittCursor CURSOR FOR select type_name,max_it_id from interfaces_type; DECLARE itCursor CURSOR FOR select it_id from interfaces where it_type=itType; declare continue handler for not found set isFinished=true; select max(it_id) into maxItId from interfaces; select max(it_id) into maxItId2 from server_interfaces; update interfaces set it_id=it_id+maxItId+maxItId2; update server_interfaces set it_id=it_id+maxItId+maxItId2; OPEN ittCursor; repeat begin FETCH ittCursor INTO itType,beginId; if not isFinished then begin open itCursor ; repeat begin fetch itCursor into itId; if not isFinished then begin update interfaces set it_id=beginId where it_id=itId; update server_interfaces set it_id=beginId where it_id=itId; set beginId=beginId+1; end; end if; end; until isFinished end repeat; close itCursor; update interfaces_type set max_it_id=beginId+1 where type_name=itType; set isFinished=false; end; end if; end; until isFinished end repeat; CLOSE ittCursor; END// delimiter ; call resetInterface();