2

English is not my native language so forgive me if something in my question is not clear, I'll try to clarify.

I have to make a change in MySQL and I'm a little bit anxious about the result since the change will be made live. I have a table that have id's from 1-800 then there is a big jump to 10000, 10001, etc. I read in multiple questions like this one that you can use 

ALTER TABLE tablename AUTO_INCREMENT = 1

My questions are :

  1. Will this reorder the current keys?
  2. What happen when my id get back up to 10000, will the ids be overwritten? (The collumn is primary)

In the case that it does reorder my keys or that they get overwritten (I can't have that happen), what would be the best solution to keep my table intact but have the id's be reset to 801? I absolutly need to keep the result untouch!!!

Thank you for taking the time to read/answer. Cheers.

Community
  • 1
  • 1
Michael Villeneuve
  • 3,933
  • 4
  • 26
  • 40
  • 2
    1. "Will this reorder the current keys?" No. 2. Read http://stackoverflow.com/questions/2615417/what-happens-when-auto-increment-on-integer-column-reaches-the-max-value-in-data. Google "auto increment wrap around" – Mike B Sep 30 '13 at 13:28
  • 1
    Changing the `AUTO_INCREMENT` value does **not** re-order ID's. – Kermit Sep 30 '13 at 13:28
  • @Michael-Villeneuve: Does it matter that the keys aren't incremental? If so then you should not be using AUTO_INCREMENT for this. Despite its name, that's not what it's for. – Strawberry Sep 30 '13 at 13:38
  • I think it does matter because my client syncs his Freshbooks invoice with the clients ID. It's easier for him to stay organised if they are one after the other. – Michael Villeneuve Sep 30 '13 at 13:52

2 Answers2

1

MySQL won't let you set the auto increment to a value which is lower than the current maximum key value. So your application will have to set the id value manually.

Marc Delisle
  • 8,879
  • 3
  • 29
  • 29
0

Use this query, changing tablename and id to the appropriate table name and column name:

update tablename set id = id - 9199 where id >= 10000

Then set your auto increment to the appropriate next value.

You might want to keep a backup copy of your table before the change.

Marc Delisle
  • 8,879
  • 3
  • 29
  • 29
  • Thank you for answering my question. However, I do not want to update the keys that are 10000+. I need to keep them there (I'm working on a terrible system). – Michael Villeneuve Sep 30 '13 at 13:54