CODEWITHSUNDEEP
mysql
user1032531
user1032531

Reputation: 26301

MySQL INSERT shows more affected rows than affected

Consider the following three queries.

The first one can only return a single row as bids_buy.id is the primary key.

The second one shows an existing record in entities_has_documents for primary key 3099541982-2536988132, and the third one doesn't execute due to that existing record.

The forth one does execute as expected, but shows two affected rows.

Why does it show two affected rows, and not just one associated with primary key 3099541982-2536988132?

mysql> SELECT bb.bids_sell_id, 2536988132,"pub_bids", NOW(),506836355 FROM bids_buy bb WHERE bb.id=2453409798;
+--------------+------------+----------+---------------------+-----------+
| bids_sell_id | 2536988132 | pub_bids | NOW()               | 506836355 |
+--------------+------------+----------+---------------------+-----------+
|   3099541982 | 2536988132 | pub_bids | 2016-04-16 08:19:13 | 506836355 |
+--------------+------------+----------+---------------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM entities_has_documents;
+-------------+--------------+----------+---------------------+--------------+-----------+------------+-----------+-------------+
| entities_id | documents_id | type     | date_added          | date_removed | added_by  | removed_by | purged_by | date_purged |
+-------------+--------------+----------+---------------------+--------------+-----------+------------+-----------+-------------+
|  2453409798 |   2536988132 | pub_bids | 2016-04-16 08:07:13 | NULL         | 506836355 |       NULL |      NULL | NULL        |
|  3099541982 |   2536988132 | pub_bids | 2016-04-16 08:18:53 | NULL         | 506836355 |       NULL |      NULL | NULL        |
+-------------+--------------+----------+---------------------+--------------+-----------+------------+-----------+-------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO entities_has_documents(entities_id,documents_id,type,date_added,added_by)
    -> SELECT bb.bids_sell_id, 2536988132,"pub_bids", NOW(),506836355 FROM bids_buy bb WHERE bb.id=2453409798;
ERROR 1062 (23000): Duplicate entry '3099541982-2536988132' for key 'PRIMARY'

mysql> INSERT INTO entities_has_documents(entities_id,documents_id,type,date_added,added_by)
    -> SELECT bb.bids_sell_id, 2536988132,"pub_bids", NOW(),506836355 FROM bids_buy bb WHERE bb.id=2453409798
    -> ON DUPLICATE KEY UPDATE type="pub_bids", added_by=506836355, date_added=NOW(), removed_by=NULL, date_removed=NULL;
Query OK, 2 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

mysql> EXPLAIN bids_buy;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| id           | int(10) unsigned | NO   | PRI | NULL    |       |
| bids_sell_id | int(10) unsigned | NO   | MUL | NULL    |       |
| stage_buy_id | int(10) unsigned | NO   | MUL | NULL    |       |
+--------------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> EXPLAIN entities_has_documents;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| entities_id  | int(10) unsigned | NO   | PRI | NULL    |       |
| documents_id | int(10) unsigned | NO   | PRI | NULL    |       |
| type         | varchar(16)      | NO   | MUL | NULL    |       |
| date_added   | datetime         | NO   |     | NULL    |       |
| date_removed | datetime         | YES  |     | NULL    |       |
| added_by     | int(10) unsigned | NO   | MUL | NULL    |       |
| removed_by   | int(10) unsigned | YES  | MUL | NULL    |       |
| purged_by    | int(10) unsigned | YES  | MUL | NULL    |       |
| date_purged  | datetime         | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+
9 rows in set (0.01 sec)

mysql>

EDIT

Per http://php.net/manual/en/pdostatement.rowcount.php

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

So, am I just seeing the number or rows returned from my SELECT statement, and not the number or rows affected by my INSERT? Why would MySQL do such a thing?

EDIT DONE

Upvotes: 2

Views: 2585

Answers (1)

geeksal
geeksal

Reputation: 5016

I think it is due to ON DUPLICATE KEY UPDATE modifier as the MYSQL Reference Manual 5.5 as well as MySQL Reference Manual 5.7 says

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. The affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.”.

In your case you already had a row with primary key value 3099541982-2536988132. Hence the MySQL lets you know that you are trying to insert a row with duplicate Primary or Unique key by indicating 2 rows affected. As the manual also say that ON DUPLICATE KEY UPDATE leads to the sequence of INSERT than UPDATE update command in case of duplicate key whereas it executes only the INSERT command in case the key is not present.

I hope this helps.

UPDATE

Also see this link.

Upvotes: 4

Related Questions