MySQL: How to re-index primary key numeration

The simplest way to fix order in primary key column is drop it and create again. Assume we have the test where id is column values not in order

create table test (id int, name varchar(64));

insert into test values (1, 'a'), (3, 'b'), (97, 'c');

select * from test;

+====+======+
| id | name |
+====+======+
| 1  | a    |
+----+------+
| 3  | b    |
+----+------+
| 97 | c    |
+----+------+

alter table test drop column id;

alter table test add column id int primary key auto_increment first;

select * from test;

After drop and re-create column we have:

+====+======+
| id | name |
+====+======+
| 1  | a    |
+----+------+
| 2  | b    |
+----+------+
| 3  | c    |
+----+------+