SQLite:REPLACE INTO如何确定行是否存在？

Question

我找到了this post解释 UPDATE 和“INSERT OR REPLACE INTO”之间的区别。它解释了这一点

INSERT OR REPLACE INTO names (id, name) VALUES (1, "John")

如果不存在 id = 1 的记录，则插入新行；如果存在，则替换 id = 1 的行。我的问题是:SQLite 如何知道或决定“id”是一个字段，其值确定记录是否已存在？

换句话说，为什么sqlite不搜索name=“John”的记录并替换id值？这是否取决于上面示例中未讨论的索引，或者 SQLite 是否对名为“id”的字段或一行字段名称中第一个命名的字段进行特殊处理？

Answer 1

请参阅CONFLICT有关如何处理此问题的子句文档。本质上，它基于 UNIQUE 和 NOT NULL 约束(主键通常作为选择是否更新或插入的约束)。

When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. If a NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with he default value for that column, or if the column has no default value, then the ABORT algorithm is used. If a CHECK constraint violation occurs, the REPLACE conflict resolution algorithm always works like ABORT.

When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.

The update hook is not invoked for rows that are deleted by the REPLACE conflict resolution strategy. Nor does REPLACE increment the change counter. The exceptional behaviors defined in this paragraph might change in a future release.