MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (id serial primary key);
insert into test values (), (), (), (), ();
delete from test where id=3;
select * from test;

id

1

2

4

5

UPDATE test 
SET id = @num := @num +1 
WHERE id > 0
ORDER BY id;

select * from test;
Data truncated for column 'id' at row 1

set @num:=0;

UPDATE test 
SET id = @num := @num +1 
WHERE id > 0
ORDER BY id;

select * from test;

SHOW CREATE TABLE test;

id

1

2

3

4

Table
Create Table

test
CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

ALTER TABLE test auto_increment = 1;

SHOW CREATE TABLE test;

Table
Create Table

test
CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci