MySQL SQLite 插入 - 重复密钥更新（UPSERT）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2717590/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)
提问by Alix Axel
MySQL has something like this:
MySQL有这样的东西：
INSERT INTO visits (ip, hits)
VALUES ('127.0.0.1', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;
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:
据我所知，SQLite 中不存在此功能，我想知道是否有任何方法可以在不必执行两个查询的情况下实现相同的效果。另外，如果这是不可能的，你更喜欢什么：
- SELECT + (INSERT or UPDATE)or
- UPDATE (+ INSERT if UPDATE fails)
- SELECT +（插入或更新）或
- UPDATE (+ INSERT如果 UPDATE 失败)
采纳答案by szmate1618
Since 3.24.0 SQLite also supports upsert, so now you can simply write the following
由于 3.24.0 SQLite 也支持 upsert，所以现在你可以简单地写如下
INSERT INTO visits (ip, hits)
VALUES ('127.0.0.1', 1)
ON CONFLICT(ip) DO UPDATE SET hits = hits + 1;
回答by dan04
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.
这要求“ip”列具有 UNIQUE（或 PRIMARY KEY）约束。
EDIT: Another great solution: https://stackoverflow.com/a/4330694/89771.
编辑：另一个很好的解决方案：https: //stackoverflow.com/a/4330694/89771。
回答by codeholic
I'd prefer UPDATE (+ INSERT if UPDATE fails). Less code = fewer bugs.
我宁愿UPDATE (+ INSERT if UPDATE fails). 更少的代码 = 更少的错误。
回答by Jacob Thomason
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...
当前答案仅适用于 sqlite OR mysql（取决于您是否使用 OR）。因此，如果您想要跨 dbms 兼容性，以下将执行...
REPLACE INTO `visits` (ip, value) VALUES ($ip, 0);
回答by Xeoncross
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.
您应该为此使用 memcached，因为它是存储单个值（访问次数）的单个键（IP 地址）。您可以使用原子增量函数来确保没有“竞争”条件。
It's faster than MySQL and saves the load so MySQL can focus on other things.
它比 MySQL 更快并节省了负载，因此 MySQL 可以专注于其他事情。
