SQLite UPSERT/更新或插入

我需要对 SQLite 数据库执行 UPSERT/INSERT OR UPDATE.

有 INSERT OR REPLACE 命令，它在很多情况下都很有用.但是，如果您想因为外键而保留带有自动增量的 id，则它不起作用，因为它删除了该行，创建了一个新行，因此该新行有一个新 ID.

这就是表格:

players - (id 的主键，user_name 唯一)

<代码>|身份证 |用户名 |年龄 |------------------------------|1982 |约翰尼|23 ||1983 |史蒂文 |29 ||1984 |佩佩|40 |

解决方案

这是一个迟到的答案.从2018年6月4日发布的SQLIte 3.24.0开始，终于支持UPSERTPostgreSQL 语法后的子句.

INSERT INTO 玩家(用户名，年龄)值('史蒂文'，32)冲突(用户名)做 UPDATE SET age=excluded.age;

<块引用>

注意:对于那些必须使用早于 3.24.0 版本的 SQLite，请参考下面的这个答案(由我@MarqueIV 发布).

但是，如果您确实可以选择升级，则强烈建议这样做，因为与我的解决方案不同，此处发布的解决方案在单个语句中实现了所需的行为.此外，您还可以获得更新版本通常附带的所有其他功能、改进和错误修复.

I need to perform UPSERT / INSERT OR UPDATE against a SQLite Database.

There is the command INSERT OR REPLACE which in many cases can be useful. But if you want to keep your id's with autoincrement in place because of foreign keys, it does not work since it deletes the row, creates a new one and consequently this new row has a new ID.

This would be the table:

players - (primary key on id, user_name unique)

|  id   | user_name |  age   |
------------------------------
|  1982 |   johnny  |  23    |
|  1983 |   steven  |  29    |
|  1984 |   pepee   |  40    |

解决方案

This is a late answer. Starting from SQLIte 3.24.0, released on June 4, 2018, there is finally a support for UPSERT clause following PostgreSQL syntax.

INSERT INTO players (user_name, age)
  VALUES('steven', 32) 
  ON CONFLICT(user_name) 
  DO UPDATE SET age=excluded.age;

Note: For those having to use a version of SQLite earlier than 3.24.0, please reference this answer below (posted by me, @MarqueIV).

However if you do have the option to upgrade, you are strongly encouraged to do so as unlike my solution, the one posted here achieves the desired behavior in a single statement. Plus you get all the other features, improvements and bug fixes that usually come with a more recent release.