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.
It is important to document the schema. Consider the following.
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
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.