By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE PROCEDURE reindex
(
IN par VARCHAR(50)
)
BEGIN
SET @newid=0;
SET @sql = CONCAT('UPDATE ', par, ' SET id=(@newid:=@newid+1) ORDER BY id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
create table test(id int, value int)
insert into test values
(1, 3),
(2, 12),
(4, 8),
(8, 7),
(11, 33)
SELECT * FROM test
| id | value |
|---|---|
| 1 | 3 |
| 2 | 12 |
| 4 | 8 |
| 8 | 7 |
| 11 | 33 |
CALL reindex('test')
SELECT * FROM test
| id | value |
|---|---|
| 1 | 3 |
| 2 | 12 |
| 3 | 8 |
| 4 | 7 |
| 5 | 33 |