CODEWITHSUNDEEP
mysql
rockstardev
rockstardev

Reputation: 13527

Changing the current count of an Auto Increment value in MySQL?

Currently every time I add an entry to my database, the auto increment value increments by 1, as it should. However, it is only at a count of 47. So, if I add a new entry, it will be 48, and then another it will be 49 etc.

I want to change what the current Auto Increment counter is at. I.e. I want to change it from 47 to say, 10000, so that the next value entered, will be 10001. How do I do that?

Upvotes: 67

Views: 57592

Answers (4)

Pascal MARTIN
Pascal MARTIN

Reputation: 400932

You can use ALTER TABLE to set the value of an AUTO_INCREMENT column ; quoting that page :

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;

There is also a note saying that :

You cannot reset the counter to a value less than or equal to any that have already been used.
For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one.
For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

Upvotes: 131

John Miller
John Miller

Reputation: 717

You can also set it with the table creation statement as follows;

CREATE TABLE mytable (
     id int NOT NULL AUTO_INCREMENT,
     ...
     PRIMARY KEY (ID)
)AUTO_INCREMENT=10000;

Hope it helps someone.

Upvotes: 0

Crickey
Crickey

Reputation: 381

you can get that done by executing the following statement 

ALTER TABLE t2 AUTO_INCREMENT = 10000;

So next Auto Increment key will start from the 10001.

I hope this will solve the problem

Upvotes: 10

Paul Dixon
Paul Dixon

Reputation: 300825

See manual for ALTER TABLE - this should do it:

ALTER TABLE [tablename] AUTO_INCREMENT = [number]

Upvotes: 20

Related Questions