MySQL - insert into... on duplicate key update - 如何区分插入或更新？

Question

我正在使用 Node.js。我使用 mysql 和 bluebird 包。

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

返回对象中应该有一个键affectedRows。从引用中可以看出，affectedRows 插入时为 1，更新时为 0 或 2。

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.

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