PHP / MySQLI: strange readings for affected_rows

Question

I'm trying to discern whether a replace into query resulted in a straight write, or first a deletion then a write.

The MySQL docs say the affected-rows count should return 1 in the case of the former, or more than 1 in the case of the latter:

The affected-rows count makes it easy to determine whether REPLACE only added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).

For me, however, doing this via PHP and MySQLI, the value is always exactly 1, regardless of whether my query results in a straight write or first a deletion then a write.

I have a table "foo" with one column: a varchar that is also the primary key. So to start off with it's empty. I run:

$sql = "REPLACE INTO foo VALUES('bar');"
$db->query($sql); //$db is an instantiated and working MySQLI instance
echo $db->affected_rows;

That gives me "1" - fair enough, it was a straight write. But if I then run the same query again, it should give me "2", right? First deleting the row, then re-inserting it, as the primary key is the same. 2 affected rows, therefore.

Incidentally, I have tried this with basic queries and also prepared statements, i.e.

$sql = "REPLACE INTO foo VALUES(?)";
$stmt = $db->prepare($sql);
$bar = "bar";
$stmt->bind_param('s', $bar);
$stmt->execute();
echo $stmt->affected_rows; //still 1

Any thoughts!?

Answer 1

Based on my experiment and findings:

Like it says in the manual, it will show you a greater number if the REPLACE has deleted a row and inserted in place instead of INSERT. Note in the docs it says:

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

Thus, in the replace statement you have to pass the value of a primary key or unique key column. From what I see, you are only passing a single value. You need to give a key value to REPLACE so that it identifies which row value should be checked before replacing/inserting.

Another interesting point is:

It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.

This results in a number more than 2 and is the case when you are passing it multiple unique column values.

---

After running a few more tests passing only unique key value like you have done, here's what I found:

1) If your other column(s) contain some value(s) (not default) and you are using the REPLACE statement passing only a unique key value, all the other column values are set to their DEFAULT values and thus the affected rows is 2. REPLACE deletes the existing row because the row contains values not mentioned in your REPLACE statement.

Note for this from the doc:

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT.

2) If all the other column values are default, using REPLACE statement passing only unique key value results in 1. Now, the reason for 1 I believe could be one of the following:

• Update has taken place (Update bar set foo=1 where foo=1 is going to return TRUE (1) on success although affected rows would be 0)
  

• REPLACE can never return 0 (returns -1 on false)

So basically, you are getting a 1 because all your other columns already have the default value set, if you change of any of the other column value and run the same statement again, you would find the result to be 2.