Rearrange primary keys in mysql

Question

How to rearrange primary key column values after deleting some rows from a table in MySQL?

Foe example; a table with 4 row of data with primary key values 1,2,3,4. When delete 2nd and 3rd rows, then the key value of 4th row change to 2.

Please help me to find solution.

Answer 1

Why do this? You don't need to rearrange your key since it's only number, identifier for record. It has no actual meaning - so let DBMS handle that. This is a very common mistake - trying to take DBMS role.

However, I'll answer your question for common case. In MySQL you can rearrange column with:

update t cross join (select @cur:=0) as init set t.col=@cur:=@cur+1

-this, however, can't be used with column under UNIQUE (so primary key as well) restriction since during update you'll possibly get duplicate records. You should drop restriction first before do that (and create it again after update).