mysql大表主键关联_mysql关联表主键重刷

无详细内容 无 --备份数据库--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();

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网