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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Hope this helps !

Method 2

See manual for ALTER TABLE – this should do it:

ALTER TABLE [tablename] AUTO_INCREMENT = [number]

Method 3

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

Method 4

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating