CODEWITHSUNDEEP
mysqlnode.jsnode-mysql
5413668060
5413668060

Reputation: 336

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

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

Upvotes: 1

Views: 837

Answers (2)

wdetac
wdetac

Reputation: 2842

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

Upvotes: 2

Rogue
Rogue

Reputation: 11483

While I'm not as savvy with this bit of node api, the basics are the same:

query(stmt, [params.col1, params.col2])
.then(results => {
    Console.log("Affected rows: " + results.affectedRows);
});

Now, the real problem is that MySQL is a fan of essentially returning garbage diagnostic information. If you modify more than 1 row, you'll have no idea what occurred on each one, thanks to this:

With ON DUPLICATE KEY UPDATE, 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 can afford it, do these statements one at a time, and check the affected row count. Otherwise, I'm digging through some MySQL internal functions but I'm not seeing much.

As a side note, you're overreaching with your wildcards there. Instead, use that space to update/insert the columns you want, and parameterize the input values:

-- Please don't name it 'table'
INSERT INTO my_table (column1, column2)
    VALUES (?, ?)
ON DUPLICATE KEY UPDATE column1 = VALUES(column1), column2 = VALUES(column2)

Upvotes: 1

Related Questions