MySQL InnoDB Reset Existing Auto-Increment Records

Posted on

Question :

Using MySQL Workbench, I have a MySQL InnoDB with a few hundred records.

The records table has the following format (id is auto_increment and unique);

+------+-------+
| id   | name  |
+------+-------+
| 2500 | jo    |
+------+-------+
| 2511 | jim   |
+------+-------+
| 2512 | sarah |
+------+-------+
| 2513 | jane  |
+------+-------+

Notice my first record begins at 2500. Presumably this is because I have dropped and re-imported the table data numerous times.

Is there any way I can ‘reset’ the id field so that the records begin at 1 and not 2500?

I want the table to look like this:

+----+-------+
| id | name  |
+----+-------+
| 1  | jo    |
+----+-------+
| 2  | jim   |
+----+-------+
| 3  | sarah |
+----+-------+
| 4  | jane  |
+----+-------+

Any advice is appreciated.

Answer :

TRUNCATE TABLE tbl;

If that does not work,

ALTER TABLE tbl AUTO_INCREMENT=1;

But you should not depend on the actual values of AUTO_INCREMENT. There are many things that can make it non-reproducible.

Leave a Reply

Your email address will not be published.