MySQL - insert into... on duplicate key update - How to distinguish between insert or update?

Question

I am using Node.js. I use mysql and bluebird packages.

const pool = mysql.createPool({ ... });

const query = (stmt, params) => {
    return Promise.promisify(pool.query, { context: pool })(stmt, params);
};

const params = { ... };

const stmt = 'insert into table set ? on duplicate key update ?';

return query(stmt, [params, params])
    .then(results => {
        // I want to know actually what is done, insert or update
    });

Answer 1

There should be a key affectedRows from the return object. From the reference, affectedRows will be 1 if it is inserted, and 0 or 2 if it is updated.

return query(stmt, [params, params])
    .then(results => {
        // I want to know actually what is done, insert or update
        if (results.affectedRows === 1) {
            // inserted
        } else {
            // updated
        }
    });

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.

Reference: https://dev.mysql.com/doc/refman/8.0/en/mysql-affected-rows.html