How to Reset auto_increment value in MySQL

Question

I am not able to reset the auto_increment value even after making changes after referring to other post

I tried :

ALTER TABLE tablename AUTO_INCREMENT = 101

ALTER TABLE users AUTO_INCREMENT=1001;

or if you haven't already added an id column, also add it

ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (id);

But still not working

Check this :

mysql> ALTER TABLE table2 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> ADD INDEX (id);
Query OK, 5 rows affected (0.17 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from table2;
+----------------+----+
| name           | id |
+----------------+----+
| Abhilash Gupta |  1 |
| John           |  2 |
| Peter          |  3 |
| Clarke         |  4 |
| Virat          |  5 |
+----------------+----+
5 rows in set (0.00 sec)

mysql> ALTER TABLE table2 AUTO_INCREMENT=101;
Query OK, 5 rows affected (0.25 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from table2;
+----------------+----+
| name           | id |
+----------------+----+
| Abhilash Gupta |  1 |
| John           |  2 |
| Peter          |  3 |
| Clarke         |  4 |
| Virat          |  5 |
+----------------+----+
5 rows in set (0.00 sec)

mysql>

I want the value of id to start from 101. Thanks in advance

The next row you add will have `id = 101`. Changing the auto increment doesn't modify existing rows, you need to use an `UPDATE` statement for that. — Barmar, Jan 02 '15 at 06:16

score 2 · Answer 1 · answered Jan 02 '15 at 06:17

2

If you want to change the existing IDs to start from 101, use:

UPDATE table2
SET id = id + 100;

The auto_increment setting is used for the ID of the next row to be added, it has no effect on existing rows.

answered Jan 02 '15 at 06:17

Barmar

741,623
53
500
612

In addition, if `id` is referenced as a foreign key from other tables, you may need to first set [`ON UPDATE CASCADE`](http://stackoverflow.com/questions/1481476/when-to-use-on-update-cascade) to ensure that the referential integrity is maintained – StuartLC Jan 02 '15 at 06:45

score 0 · Answer 2 · answered Jan 02 '15 at 06:19

Follow this link for reference to AUTO INCREMENT

Now what you are doing is i think correct, but the changes are not reflected because you did not try to enter a new row to the database. Alter command changes the AUTO INCREMENTS value but that will only be reflected in the next insert to the database. It will not affect the data that is already present in the TABLE. Try entering a new row to the DB and check if the ID Value changes.
If not then post the output after entering that row.

score 0 · Answer 3 · edited Jul 29 '18 at 14:01

0

This is how it should be written

cur.execute('''ALTER TABLE tablename AUTO_INCREMENT=0''')

edited Jul 29 '18 at 14:01

Mark Rotteveel

100,966
191
140
197

answered Jul 29 '18 at 12:36

Amit Agrawal

1

Using all capitals is considered shouting. It is rude. I have edited your answer to fix this, but in the future please make sure to avoid that and instead use proper capitalization. – Mark Rotteveel Jul 29 '18 at 14:02

How to Reset auto_increment value in MySQL

3 Answers3