mysql关联表主键重刷

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql关联表主键重刷，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3576字，纯文字阅读大概需要6分钟。

内容图文

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

内容总结

以上是互联网集市为您收集整理的mysql关联表主键重刷全部内容，希望文章能够帮你解决mysql关联表主键重刷所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---