REPLACE INTO, does it re-use the PRIMARY KEY?

Question

The REPLACE INTO function in MySQL works in such a way that it deletes and inserts the row. In my table, the primary key (id) is auto-incremented, so I was expecting it to delete and then insert a table with id at the tail of the database.

However, it does the unexpected and inserts it with the same id! Is this the expected behaviour, or am I missing something here? (I am not setting the id when calling the REPLACE INTO statement)

Answer 1

This is an expected behavior if you have another UNIQUE index in your table which you must have otherwise it would add the row as you would expect. See the documentation:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, “INSERT Syntax”.

https://dev.mysql.com/doc/refman/5.5/en/replace.html

This really also makes lot of sense because how else would mySQL find the row to replace? It could only scan the whole table and that would be time consuming. I created an SQL Fiddle to demonstrate this, please have a look here