SQLite 插入 - 重复密钥更新(UPSERT)

2021-11-20 00:00:00 sql database upsert mysql sqlite

MySQL 有这样的东西:

MySQL has something like this:

INSERT INTO visits (ip, hits)
VALUES ('127.0.0.1', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;

据我所知 SQLite 中不存在此功能，我想知道是否有任何方法可以在不必执行两个查询的情况下达到相同的效果.另外，如果这是不可能的，你更喜欢什么:

As far as I know this feature doesn't exist in SQLite, what I want to know is if there is any way to achive the same effect without having to execute two queries. Also, if this is not possible, what do you prefer:

  1. SELECT +(插入或更新)或
  2. 更新(+ INSERT 如果更新失败)

推荐答案

自 3.24.0 SQLite 也支持 upsert，所以现在你可以简单地写下

Since 3.24.0 SQLite also supports upsert, so now you can simply write the following

INSERT INTO visits (ip, hits)
VALUES ('127.0.0.1', 1)
ON CONFLICT(ip) DO UPDATE SET hits = hits + 1;

相关文章