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
– Barmar, Commented Jan 2, 2015 at 6:16

Barmar · Accepted Answer · 2015-01-02 06:17:23Z

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 2, 2015 at 6:17

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StuartLC Over a year ago

In addition, if id is referenced as a foreign key from other tables, you may need to first set ON UPDATE CASCADE to ensure that the referential integrity is maintained

Rhythem Aggarwal · Accepted Answer · 2015-01-02 06:19:06Z

0

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.

answered Jan 2, 2015 at 6:19

Rhythem Aggarwal

3562 silver badges16 bronze badges

Comments

Mark Rotteveel · Accepted Answer · 2018-07-29 14:01:43Z

0

This is how it should be written

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

edited Jul 29, 2018 at 14:01

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Jul 29, 2018 at 12:36

Amit Agrawal

1

1 Comment

Mark Rotteveel Over a year ago

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.

Collectives™ on Stack Overflow

How to Reset auto_increment value in MySQL

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related