Change an autoincrementing field to one previous

One day, wordpress suddenly jumped from pots id 9110 to 890000000 post. Days later I'd like to move back new posts to continue from id 9111. I'm sure that id will neve开发者_开发问答r reach id 890000000, no problem here, but id is an autoincrement field and ALTER TABLE wp8_posts AUTO_INCREMENT = 9111 is not working.

Can I force id to continue from 9111 ?

You probably need to renumber the already existing post so that it has an id of 9111, and then issue your alter table command. You'll have to change the ID in all the other tables too that are pointing to this ID. If you then issue your alter table command, it should work. If this still doesn't work, you could rename the table, to something like wp8_posts_backup, with:

RENAME TABLE wp8_posts TO wp8_posts_backup

Then, create another table with the same schema with:

Create Table wp8_posts LIKE wp8_posts_backup;

and then copy the data from the backup to the new one. All this still requires renumbering the old post, or deleting it and then recreating it, because having the database knows that there is a record with ID of 890000000, and will always try to go above that when creating the next ID. I believe it uses the index on the column to find the highest number, and calcuate the next id, rather than storing the same value somewhere else. Which is why it's necessary to have a unique index on any autoincrementing field.