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