Reset MySQL auto_increment when a large number already exists?

Question

I have a table with an auto incrementing column. I need to set one of the rows to 1000 and never touch it again, but now that I've set it to 1000, auto increment keeps starting at 1001 and refuses to start at 1. Is there any way to get around this?

If you *could* get this to work, what would you expect to happen once the auto-increment reaches 1000 again? — Oliver Charlesworth, Jan 20 '11 at 08:21

score 8 · Answer 1 · answered Jan 20 '11 at 09:46

You cannot:

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;

You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

score 3 · Answer 2 · edited Feb 18 '13 at 14:25

3

Simple and short answer: you can't do this.

If you could, what would happen if you start your auto-increment at 1 and insert 1000 rows? The last couldn't be inserted due to "duplicate key"-error.

If you have to have a predefinded entry, with an id that never changes and is easy to remember, why don't you use 0 for that? Or, if you really need to use 1000, what's the problem with letting the other columns start at 1001?

edited Feb 18 '13 at 14:25

Gert Arnold

105,341
31
202
291

answered Jan 20 '11 at 08:39

oezi

51,017
10
98
115

He shouldn't but he can't. See my answer. – Álvaro González Jan 20 '11 at 10:23
this was ajreal editing my answer, i wrote "can't" - and now i rolled back that edit – oezi Jan 20 '11 at 10:32

score 1 · Answer 3 · answered Aug 30 '13 at 09:31

Assuming you have no other row ID with 1000, you can insert the row to the bottom of the table, then you can simply use the update command:

UPDATE  table.column SET id = 1000 WHERE id = current_id;

Assuming id is your auto-increment column. And current_id should be replaced with the id that the row is inserted at.

score 0 · Answer 4 · edited Oct 20 '12 at 04:20

0

You can use MS Access that link to MySQL as external table, and you can change the auto increment table field value from MS Access via copy paste from Excel (first, you need to arrange the value of auto increment in Excel).

edited Oct 20 '12 at 04:20

xdazz

158,678
38
247
274

answered May 31 '12 at 06:22

cogito

1

score 0 · Answer 5 · edited Feb 02 '14 at 07:53

0

You could use the following statements:

UPDATE tbl SET id=1000 WHERE id=current_id;
ALTER TABLE tbl AUTO_INCREMENT=1001;

edited Feb 02 '14 at 07:53

László Papp

51,870
39
111
135

answered Feb 02 '14 at 07:32

user3095614

11
2

Reset MySQL auto_increment when a large number already exists?

5 Answers5