How to get list of created/updated indexes from INSERT INTO with ON DUPLICATE KEY UPDATE in SP [MySQL 8.0.36]

Question

How does one get a list of the record IDs created or updated when an INSERT INTO with ON DUPLICATE KEY is used - within the very stored procedure that executes the INSERT?

For example, such an action could update records 5,23,91, and create 92,93,94,95.

ROW_COUNT() and LAST_INSERT_ID() isn't enough to do this, and I don't see a function available to MySQL that presents the same as mysql_affected_rows().

So how can I get a list of the row IDs that were added and updated with this scenario?

Answer 1

You may obtain needed data using user-defined variables and triggers.

Sample code:

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  val INT
);
CREATE TRIGGER tr_ins -- save id of the inserted row
AFTER INSERT ON test
FOR EACH ROW
SET @inserted := CONCAT_WS(',', @inserted, NEW.id);
CREATE TRIGGER tr_upd -- save id of the updated row
AFTER UPDATE ON test
FOR EACH ROW
SET @updated := CONCAT_WS(',', @updated, NEW.id);

-- clear user-defined variables which will collect the list of effected rows
SET @inserted := NULL;
SET @updated := NULL;

INSERT INTO test VALUES
(1,1),    -- explicit insert
(NULL,2), -- autogenerated insert
(NULL,3), -- autogenerated insert
(2,4),    -- update
(1,5),    -- update
(2,6),    -- update
(NULL,7), -- autogenerated insert
(5,8),    -- explicit insert
(3,9)     -- update
ON DUPLICATE KEY UPDATE val = VALUES(val);
SELECT * FROM test;
-- see the lists of inserted/updated row's id values
SELECT @inserted, @updated;

Records: 9  Duplicates: 4  Warnings: 1

id	val
1	5
2	6
3	9
4	7
5	8

@inserted	@updated
1,2,3,4,5	2,1,2,3

fiddle