If I execute the following statement:
INSERT INTO users(id, username) VALUES(102, 'test') ON DUPLICATE KEY UPDATE username='test';
And if the value for column id
exists in DB, but the value for username
is different, then the reported number of affected rows is 2. To be more precise:
What is the reason for this? Is this to provide information back of what action took place? Are there actually 2 rows being affected?
Tested on MariaDB 10.3.7 with
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(355) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8mb4;
INSERT INTO `users` (`id`, `username`) VALUES (102, 'Some name');
INSERT... ON DUPLICATE KEY UPDATE
executes two actions. You can demonstrate this with triggers that set session variables as a side-effect:
mysql> create trigger i before insert on users for each row set @i = true;
mysql> create trigger u before update on users for each row set @u = true;
mysql> INSERT INTO users(id, username) VALUES(102, 'test') ON DUPLICATE KEY UPDATE username='test';
Query OK, 2 rows affected (0.01 sec)
mysql> select @i, @u;
+------+------+
| @i | @u |
+------+------+
| 1 | 1 |
+------+------+