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 ;