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();