如何在不存在时插入并在存在时更新(upsert)?

问题描述：

我目前对以下内容感到困惑:

I am currently having a confusion with:

var post = {ip: host, status: info};
var sql = con.query('INSERT OR IGNORE INTO ipaddress SET ?', post, 
function(err, result){});

我已经将 IP 地址设置为主键.

I have already set the IP address to primary key.

答

Sequelize

既然你在使用 node.js，那么使用 Sequelize 怎么样?

Sequelize 是一个基于 Promise 的 ORM，适用于 Node.js v4 及更高版本.它支持 PostgreSQL、MySQL、SQLite 和 MSSQL 方言，并具有可靠的事务支持、关系、读取复制等功能.

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite and MSSQL and features solid transaction support, relations, read replication and more.

如果您不了解 SQL 也没关系，因为您可以使用它的内置函数.例如，如果你想执行一个 upsert:

It doesn't matter if you don't know SQL well because you can use its built-in functions. For example, if you want to perform an upsert:

ipaddress.upsert({
    ip: host,
    status: info,
});

纯 MySQL

如果您仍想使用 MySQL，请使用 ON DUPLICATE KEY UPDATE 执行更新插入.

var sql = `INSERT INTO post (ip, status) VALUES (${host}, ${info}) ON DUPLICATE KEY UPDATE ip=${host}, status=${info}`;

但是你需要注意一些潜在的问题，比如 SQL 注入.

But you need to take care some potential issues like SQL injection.

如何在不存在时插入并在存在时更新(upsert)?

Sequelize

纯 MySQL

相关推荐