Inserting into the middle of MYSQL table with auto-increment

Question

Say I have a table with 2 fields, "word_id" and "word", which contains every work from some literary classic. I insert words by just typing:

INSERT INTO equus(word) VALUES('horse');

And the word_id field is auto-incrementing the whole time, keeping track of where each word falls in the books in comparison to one another. Oh no, I missed a word on the first page! Is there a way to insert a record at a given location (say after word_id = 233) and have every word after it readjust so that all the records remain in order?

Answer 1

This can be done as two queries using an update with an order by clause.

Given some INSERT_POS to insert the value at:

UPDATE `equus` 
  SET `word_id` = `word_id` + 1 
  WHERE `word_id` >= INSERT_POS 
  ORDER BY `word_id` DESC;
INSERT INTO `equus`(`word_id`, `word`) VALUES(INSERT_POS, 'TheWord');