mysql add an autoincrement column with start value

Question

I would like to add a new autoincrement column to a pre-existing table. However, I need the ids that are in that column to start at a particular value, ie

alter table MyTable 
add column MyColumn int unsigned not null primary key auto_increment=999999;

Is this possible? I tried issuing:

alter table MyTable auto_increment=999999;

before I added the column, but it had no effect. Because adding the column will automatically generate the ids, it is not sufficient to run the second statement after the first.

Bill Karwin · Accepted Answer · 2014-03-25 21:53:21Z

3

No, it works to add an AI column with a starting position. But you almost got the syntax right. Here's a demo:

mysql> CREATE TABLE foo (v varchar(10));

mysql> INSERT INTO foo VALUES ('one'), ('two'), ('three');

Then comes the tricky syntax. You have to declare the column as AUTO_INCREMENT, but then also give the table option for the AUTO_INCREMENT starting value. And you need a comma to separate the ADD COLUMN from the table option.

mysql> ALTER TABLE foo ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY,
    AUTO_INCREMENT=999999;

mysql> select * from foo;
+-------+---------+
| v     | id      |
+-------+---------+
| one   |  999999 |
| two   | 1000000 |
| three | 1000001 |
+-------+---------+

answered Mar 25, 2014 at 21:53

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

Denise Over a year ago

Nice! I didn't realize you could combine statements like that. (That is what you're doing, right?)

Bill Karwin Over a year ago

Yes. Multiple ALTER TABLE changes in one command, separated by commas. People often overlook that you can do that, and they end up running multiple time-consuming ALTER TABLE operations when they only had to do one.

Collectives™ on Stack Overflow

mysql add an autoincrement column with start value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related