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

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.