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.

score 3 · Answer 1 · answered Aug 02 '11 at 10:49

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.

answered Aug 02 '11 at 10:49

womble

95,029
29
173
228

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 06:15
Ask new questions in new questions, not as comments to existing answers. – womble Aug 14 '11 at 06:17

score 1 · Answer 2 · answered Aug 02 '11 at 10:50

1

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.

answered Aug 02 '11 at 10:50

Sven

97,248
13
177
225

score 0 · Answer 3 · answered Aug 02 '11 at 17:16

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.

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

3 Answers3