How to set auto_increment to start at a given number?

upwordz;209090 wrote: I'm working with a table that has about 50 columns in it. One of those columns is the orders_id field which is the primary key. We are going to be transitioning from one webserver to another. So, for a time, we may be receiving orders from customers from both systems. We want to set the new server to use a higher range of orders_id numbers. We queried the last id number as 10064131 and want to start the new number at 11006300. We received one tip already which was something along the lines of "insert all the records between 10064131 and 11006300". The other thought was just try inserting an order with an id of 11006300, then delete it.

I'm pretty new to mysql so I'd love any help before I start hacking away on it.

I figured I could start inserting records but without any short cuts, that would take forever if I had to include each column in each insert statement.

So, I thought maybe I could set the auto_increment value to where I wanted it. I tried:

mysql> alter table orders auto_increment=11006300;
Query OK, 12480 rows affected (0.67 sec)
Records: 12480 Duplicates: 0 Warnings: 0

But then when I checked the last record:

mysql> select max(orders_id) from orders;
+----------------+
| max(orders_id) |
+----------------+
| 10064131 |
+----------------+
1 row in set (0.00 sec)

So, I don't think my alter table command worked. Probably because it was intended to be used for existing values.

Any clues or thoughts are appreciated!

Darren

Hi Darren,

Although I'm not a mysql pro, I noticed something was odd. According to what I know (correct me if I'm wrong), mysql shouldn't have any "rows affected" when you run
mysql> alter table orders auto_increment=11006300;'

It should just set the next auto incremented (order_id) to 11006300 (i.e. the next order would have order_id = 11006300). Your original data should be be altered in any way. The output of phpmyadmin on my test database was: Your SQL query has been executed successfully (Query took 0.0859 sec)'

As for migrating the existing data into the new database, you might find the following helpful: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html
(maybe with the --ignore-lines=N option)

Eric

P.S. Did I answer your question? Good luck with migrating your data.