【数据库-MySql】对指定的表的键值重新设置序号

 存储过程 reorder 

DROP PROCEDURE IF EXISTS reorder;
delimiter $$
CREATE PROCEDURE reorder(IN tbName varchar(100),IN colName varchar(100))     
BEGIN
	DECLARE SQL_TEXT1 VARCHAR(500);
	DECLARE SQL_TEXT2 VARCHAR(500);
	DECLARE SQL_TEXT3 VARCHAR(500);
	DECLARE nR INT;
	DECLARE I INT;
		
		SET SQL_TEXT1 =CONCAT('SELECT COUNT(*) into @numRow From ',tbName);
		SET SQL_TEXT2 =CONCAT('Update ',tbName,' SET ',colName,'=',colName,'+1000000 ORDER BY ',colName,' DESC;');
		
	  -- 执行定义语句 SQL_TEXT1
		SET nR=  @numRow;
		-- 执行定义语句 SQL_TEXT2
		SELECT SQL_TEXT2;
	
	-- 	SELECT @numRow INTO nR;
  IF nR is not NULL THEN 
		SET I = 1;
		WHILE I < nR DO
			SET SQL_TEXT3 =CONCAT('UPDATE ',tbName,' SET ',colName,'  = ',I,' WHERE ',colName,'  >=',I,' limit 1;');
			-- 执行SQL_TEXT3
			SET @tmp = SQL_TEXT3;
			PREPARE exeSql from @tmp;
			EXECUTE exeSql;
			DEALLOCATE PREPARE exeSql;
		 SET I=I+1;
		END WHILE;
	end if;
END  $$
delimiter ; 

 调用示例

CALL reorder('table_temp','id');

设置序号前 

重新设置序号后