mysql关联表主键重刷

--备份数据库
--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();