无详细内容 无 –备份数据库–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
<style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .本文来源gao.dai.ma.com搞@代*码#网CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms
- –> <!—ecms
- $velocityCount
–> <!—ecms
–>
--备份数据库--mysqldump -h localhost -uroot -p123456 database > dump.sql--初始化interfaceType--先处理掉select it_id ,count(*) as sum from server_interfaces group by it_id having sum>1drop 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关联表主键重刷
