# MYSQL Table Reset Auto Increment to 1, while retaining existing records with ids

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.

-

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.

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

-

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.

-