MySQL ID starts to increase from 1 to quickly solve the problem of discontinuous ID

mysql id starts from 1 and increases automatically to solve the problem of discontinuous id

As a person with obsessive-compulsive disorder, I cannot tolerate the problem of discontinuous ids after deleting some rows in the table. At first, I used

TRUNCATE TABLE tablename

To achieve the self-increment of id from the beginning, but this command will also clear the entire table, which is really a pitfall.

Later I found the correct approach:

alter table tablename auto_increment = 1;

This command will not change the content and order of the existing table. At the same time, the id of the newly inserted row will first use the deleted id to perfectly fill the vacant id.

MySQL auto-increment id jump solution (without deleting data)

Problem description:

I'm importing a large amount of data from Excel to MySQL. The auto-increment ids are not incremented in sequence, and many are skipped in the middle. I don't want to re-import them because of the huge amount.

Solution:

Sort by id or time from small to large ==> Get the row number ==> Replace the original id with the row number

SQL statement:

update tab as t1 join (select id,(@rowno:=@rowno+1) as rowno from tab a,(select (@rowno:=0)) b order by a.id) as t2 SET t1.id=t2.rowno WHERE t1.id=t2.id;

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.