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 ;
