mysql: Reset counter after deleting rows

Question

Dropping or truncating a table resets the counter(AUTO_INCREMENT), but deleting selected rows (with a WHERE clause) doesn’t reset the counter.

I want it to continue it from the point where rows were deleted.

Is there any way to reset the counter? Please follow the example below for better understanding.

mysql> create table dummy(id int NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (2.09 sec)

mysql> insert into dummy values (),(),(),(),(),();
Query OK, 6 rows affected (0.19 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

mysql> delete from dummy where id>4;
Query OK, 2 rows affected (0.23 sec)

mysql> select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> insert into dummy values (),(),(),(),(),();
Query OK, 6 rows affected (0.18 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
+----+
10 rows in set (0.00 sec)

What I want:

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

Its different from https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql — Agniveer, May 28 '18 at 00:36
@pradeepTuran007 please post as your answer how it worded, for future readers. — Mohit Kumar, May 28 '18 at 00:54

score 1 · Accepted Answer · answered May 28 '18 at 01:29

We can query alter table dummy AUTO_INCREMENT=1; after delete statement;

So, as soon as we add new values to the table, it will check in the existing table, what value should be assign to the id starting from 0, and will assign the one which is exactly 1 greater than the max id of the table.

So, it will be like:

mysql> delete from dummy where id>4;
Query OK, 2 rows affected (0.14 sec)

mysql> select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> alter table dummy AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into dummy values (),(),(),(),(),();
Query OK, 6 rows affected (0.18 sec)
Records: 6  Duplicates: 0  Warnings: 0

And we will get in sequential form:

select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

Thanks @jspcal

mysql: Reset counter after deleting rows

1 Answers1