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

MySQL 是这样的:

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

据我所知，这个特性在 SQLite 中不存在，我想知道的是，是否有办法在不执行两个查询的情况下实现相同的效果。此外，如果这不可能，你更喜欢什么:

SELECT + (INSERT 或 UPDATE) 或
更新(+ 插入 如果更新失败)

111623 次浏览

小开

I'd prefer UPDATE (+ INSERT if UPDATE fails). Less code = fewer bugs.

小开

INSERT OR IGNORE INTO visits VALUES ($ip, 0);
UPDATE visits SET hits = hits + 1 WHERE ip LIKE $ip;

This requires the "ip" column to have a UNIQUE (or PRIMARY KEY) constraint.

EDIT: Another great solution: https://stackoverflow.com/a/4330694/89771.

小开

You should use memcached for this since it is a single key (the IP address) storing a single value (the number of visits). You can use the atomic increment function to insure there are no "race" conditions.

It's faster than MySQL and saves the load so MySQL can focus on other things.

小开

The current answer will only work in sqlite OR mysql (depending on if you use OR or not). So, if you want cross dbms compatibility, the following will do...

REPLACE INTO `visits` (ip, value) VALUES ($ip, 0);

小开

最佳答案

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;