15

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)

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
matt
  • 2,857
  • 7
  • 33
  • 58
  • 6
    Cannot reproduce: `CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL UNIQUE, value VARCHAR(200) NOT NULL); REPLACE INTO test (name, value) VALUES ('foo', 'bar'); REPLACE INTO test (name, value) VALUES ('foo', 'baz'); SELECT id FROM test;` returns 2, not 1 — apparently `id` has been incremented twice. (edit: tested with both InnoDB and MyISAM) – lanzz Aug 30 '12 at 20:47
  • What storage engine are you using on this table? – Mike Brant Aug 30 '12 at 21:03
  • The behavior you are observing is not consistent with the expected behavior. One possible explanation is that the DELETE operation does not succeed, possibly due to a foreign key constraint violation. That's the only thing I can think of. Can't do much more to help without a more substantial test case that demonstrates the observed behavior. – spencer7593 Aug 30 '12 at 21:51
  • Another explanation for the observed behavior is a wonky BEFORE INSERT trigger that's assigning a value to the `id` column. But that would be very unusual. – spencer7593 Aug 30 '12 at 22:02
  • 2
    This is madness. I actually do want the behavior that the OP is talking about, where the same id is kept for a replace statement instead of being updated!! – Spencer Williams Aug 22 '16 at 19:07

2 Answers2

14

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

hol
  • 8,255
  • 5
  • 33
  • 59
  • 2
    The OP specifies that he is not providing a value for the `id` column. If the statement was providing a value for the `id` column, then yes, we would expect the value to be "reused". But if no value is provided, we expect normal AUTO_INCREMENT behavior. – spencer7593 Aug 30 '12 at 22:02
  • 1
    Indeed, I have another `UNIQUE` index as you have described. Many thanks for the help. :) – matt Sep 01 '12 at 13:58
3

That is expected behavior. Technically, in cases where ALL unique keys (not just primary key) on the data to be replaced/inserted are a match to an existing row, MySQL actually deletes your existing row and inserts a new row with the replacement data, using the same values for all the unique keys. So, if you look to see the number of affected rows on such a query you will get 2 affected rows for each replacement and only one for the straight inserts.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • 1
    That doesn't really explain why an ID would be reused. – Robert Harvey Aug 30 '12 at 20:50
  • Actually, the asker (rightly) expects his autoincrement `id` to be allocated anew with the replace, but he observes a preserved `id`. – lanzz Aug 30 '12 at 20:50
  • 2
    Do you have a test case that demonstrates this behavior? Your explanation of the behavior is contrary to the behavior I have experienced, with both MyISAM and InnoDB. – spencer7593 Aug 30 '12 at 22:11