mysql add an autoincrement column with start value

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.

Solution

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 |
+-------+---------+