Reset MySQL auto_increment when a large number already exists?

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?

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

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?

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.

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

You could use the following statements:

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