mysqlsql-inserton-duplicate-key

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


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?


Solution

  • 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