使用存储过程迁移表数据

1.表结构使用的是联合主键：key1,key2

key1自增

2.目的：将key2替换成表中已经存在的值，由于替换后存在主键冲突。所以。需要将自增字段偏移一定量，进行添加，同时添加完成后修改自增起始量；

DELIMITER $$

USE `database`$$

DROP PROCEDURE IF EXISTS `move`$$

CREATE DEFINER=`root`@`%` PROCEDURE `move`(IN t_name VARCHAR(40),IN pri_name VARCHAR(20))
BEGIN
#查询待迁移表当前最大数 付给 currentRowNum
SET @currentRowNum = 0;
SET @offsetvalue = 1000;
SET @_sql  = '';
SET @sqlStmt = CONCAT('SELECT MAX(',pri_name,')+1 INTO @currentRowNum from ',t_name,';');
    PREPARE stmt1 FROM @sqlStmt;
    EXECUTE stmt1;
#SELECT MAX(pri_name)+1 into @currentRowNum  FROM t_name ;
#currentRowNum +offset当做偏移量
SET @currentRowNum=@currentRowNum + @offsetvalue;

#insert into  select 


SET @sqlinsertSelect = CONCAT('SELECT CONCAT("insert into ",table_name," (",
GROUP_CONCAT(column_name),",citycode) select ",
GROUP_CONCAT(select_column_name),",110100 from ",table_name
," where citycode =110200; ") INTO @_sql FROM 
(SELECT table_name,column_name,CONCAT(column_name,"+",@currentRowNum) AS select_column_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "database"    AND EXTRA="auto_increment" AND table_name = "',t_name,'" UNION ALL
SELECT table_name,GROUP_CONCAT(column_name) AS column_name,GROUP_CONCAT(column_name) AS select_column_name
 FROM information_schema.`COLUMNS` 
WHERE table_schema = "database"   AND column_key!="PRI" AND table_name = "',t_name,'" GROUP BY table_name
) t
GROUP BY t.table_name;');
#select @sqlinsertSelect;

 PREPARE stmt2 FROM @sqlinsertSelect;
    EXECUTE stmt2;




PREPARE STMT3 FROM @_sql;
EXECUTE STMT3 ;

#查询迁移之后表的最大数
PREPARE stmt4 FROM @sqlStmt;
    EXECUTE stmt4;
#修改递增ID

SET @_sql1 = CONCAT('ALTER TABLE ',t_name,' AUTO_INCREMENT= ',  @currentRowNum, ';'); 


 PREPARE STMT5 FROM @_sql1; 
 EXECUTE STMT5 ;
    END$$

DELIMITER ;