mysqlon-duplicate-key

Why does INSERT ... ON DUPLICATE KEY UPDATE affected rows reports 2?


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');

Solution

  • 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 |
    +------+------+