SQL INSERT ... ON DUPLICATE KEY - return number of only newly inserted rows, not number of newly inserted + updated rows?

Question

How can I get this? I know I can do this by getting COUNT of all rows before running this Insert query, and then substract it from COUNT of all rows after running this Insert query, but it's not practical for big databases. I also found this formula on stackoverflow.com

(numberOfValuesInInsert * 2) - mysql_affected_rows()

but it doesn't seem to work for me, as I get affected rows = 1 no matter if I update or insert new row

Here is my node.js code running inside an FOR loop, so after ending of loop I get sum of affected rows:

var sql = "INSERT INTO classdata (id, siteid, name, staff, locationname, start, end) VALUES ?
ON DUPLICATE KEY UPDATE 
siteid=VALUES(siteid), name=VALUES(name), staff=VALUES(staff), locationname=VALUES(locationname), start=VALUES(start), end=VALUES(end)";
var values = [[id, siteId, name, staff, locationName, start,end]];

connection.query(sql, [values],function(err, results) {
            if (!err) {console.log(new Date() + " : New data sucessfully inserted into classdata table. Affected rows: " + results.affectedRows);}});
affectedrows+= results.affectedRows;

And it always prints Affected rows: 1, no matter if record is inserted or updated..And I read here that it should return 2 for each updated record and 1 for inserted. Getting number of rows inserted for ON DUPLICATE KEY UPDATE multiple insert?

I have one PK, it is "id" field if that means something to you.

score 1 · Accepted Answer · answered Dec 25 '15 at 07:16

It is important to document the schema. Consider the following.

Schema

create table t1
(   id int auto_increment primary key,
    thing varchar(100) not null,
    timesHere int not null
);

create table t2
(   id int auto_increment primary key,
    thing varchar(100) not null,
    timesHere int not null,
    unique key(thing)
);

create table t3
(   id int auto_increment primary key,
    thing varchar(100) not null,
    timesHere int not null
);

and this PHP (note I went with old mysql functions to merely demonstrate, not that I would ever use that library)

But, it has the mysql_affected_rows function in it that matches your name.

PHP example

<?php
    error_reporting(E_ALL); 
    ini_set('display_errors', 1);
    $dbname = 'so_gibberish';
    $dbuser = 'myuser';
    $dbpass = 'mypassword';
    $dbhost = 'localhost';
    $link = mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
    mysql_select_db('so_gibberish') or die("Could not open the db");
    $test_query = "insert t1(thing,timesHere) values ('frog',1) on duplicate key update timesHere=timesHere+1";
    $result = mysql_query($test_query) or die(mysql_error());
    $numrows=mysql_affected_rows($link);
    echo "affected rows=".$numrows."<br/>";
    mysql_close($link);

If I run the above, it will always return a 1 for affected rows because I have not inserted a duplicate value in an index that would cause a unique key clash.

Remember, it is the unique key clash that causes the update to fire, as opposed to continuously inserting rows.

But if I were to modify the PHP and use table t2, then the first run would be affected rows=1, and thereafter with the same data affected rows=2

It doesn't matter what your actual client side code is, you should see these results if you have a keen eye as to your schema

What also can factor into it is the value of the CLIENT_FOUND_ROWS flag. See the following link regarding that.

Mysql manual page on mysql_affected_rows()

An excerpt:

For INSERT ... ON DUPLICATE KEY UPDATE statements, 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, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

What about table t3 in the schema above?

Well, if my insert stmt from the PHP file would have been

$test_query = "insert t3(id,thing,timesHere) values (1,'frog',1) on duplicate key update timesHere=timesHere+1";

Then yes, the results would have been the same. 1 the first time testing, affected rows=2 thereafter.

What is the point of the column named timesHere ?

That is there to prove the increment works and the Update happened.

I will not be at my workstation until tomorrow, but looking at your answer it looks like that CLIENT_FOUND_ROWS flag is the issue. I'm using require('mysql') in node.js. I'll see what I can do tomorrow and reply. Thanks for your effort! — kecman, Dec 25 '15 at 18:12
It was FOUND_ROWS flag that needed to be added to connection var. Now my connection var looks like this: var connection = mysql.createConnection({ host: dbhost, user: dbuser, password: dbpassword, database: dbname, flags: '-FOUND_ROWS' }); And now when row is updated value is 2, when inserted 1, and when updated to current values it is 0. — kecman, Dec 27 '15 at 20:19

SQL INSERT ... ON DUPLICATE KEY - return number of only newly inserted rows, not number of newly inserted + updated rows?

1 Answers1

Schema

PHP example