Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a MySQL table naming Product for a classified site, product_id is bigint(20) AUTO_INCREMENT field.

Currently AUTO_INCREMENT value is 224143.

Client want it to restart from 1. but this table has 7000 existing STARTING from prduct_id 210235 to 224143. User can manage their items by using its id, i can not revise, regenerate ids, but want to restart next AUTOINCREMENT ENTRY from 1, and hold the existing record as it is.

Thanks in advance.

share|improve this question

3 Answers 3

You can't. MySQL will automatically set the auto_increment value to MAX(column)+1 even if you've used alter table tbl auto_increment = 1. And that's a restriction with a very valid rationale -- duplicating keys never ends well.

share|improve this answer
    
yes, i already try to update last Auto increment value, as you said it take max+1. OK i am leaving it as it is, As if restarted, soon there will be clash when MAX+1 will be equal to existing ID(This may not occurs as i will delete existing data after 1 year.), But i have another thing in mind INTEGER WRAPAROUND After reaching Max value there would be overflow, do you know the behavior of mysql (Whether it will generate next ID to -ve or restart from 1 IN-CASE of Reaching maximum value)? (SORRY for writing as a blog) –  Asad kamran Aug 14 '11 at 6:15
    
Ask new questions in new questions, not as comments to existing answers. –  womble Aug 14 '11 at 6:17

First, I think this is a terrible idea and you are only asking for trouble if you do this.

Second, I am not sure it is at all possible to restart the auto_increment counter back to 1, as MySQL will prevent it for good reasons.

share|improve this answer

I think your best bet to get the behavior you want would be to export the data to a file (mysql -Bse "select * from table" will export tab separated by default) and import the data into a new table, either rewrite the ID number using awk/perl or re-import the data without the ID column and let it auto_increment from 1 on the new table. Be advised you will have to fix any relationships that other tables have to the ID number of this table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.