sqlite on duplicate key update

SQLite本身并不直接支持ON DUPLICATE KEY UPDATE语句，这是MySQL中的一个特性。但在SQLite中，可以通过使用INSERT OR REPLACE或INSERT OR IGNORE结合唯一约束来实现类似的功能。

基础概念

唯一约束（Unique Constraint）：确保表中的某列或多列的值是唯一的。
INSERT OR REPLACE：如果插入的记录与现有记录冲突（基于唯一约束），则删除现有记录并插入新记录。
INSERT OR IGNORE：如果插入的记录与现有记录冲突，则忽略该插入操作。

类型与应用场景

INSERT OR REPLACE：适用于当你希望在记录存在时更新它，不存在时插入新记录的场景。
INSERT OR IGNORE：适用于当你希望在记录存在时忽略插入操作，避免错误或重复数据的场景。

示例代码

假设我们有一个名为users的表，结构如下：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

使用 INSERT OR REPLACE

INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'John Doe', 'john.doe@example.com');

这条语句会检查email列是否有唯一约束冲突。如果有，则删除旧记录并插入新记录。

使用 INSERT OR IGNORE

INSERT OR IGNORE INTO users (id, name, email)
VALUES (1, 'John Doe', 'john.doe@example.com');

这条语句会检查email列是否有唯一约束冲突。如果有，则忽略这次插入操作。

遇到的问题及解决方法

问题：如何处理复杂的更新逻辑？

如果需要在更新时执行更复杂的操作，可以使用CASE语句或其他逻辑来处理。

INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'John Doe', 'john.doe@example.com')
ON CONFLICT(email) DO UPDATE SET
    name = excluded.name,
    email = excluded.email;

在这个例子中，excluded是一个特殊的表，包含了因为冲突而被排除的行。

问题：如何避免误删除数据？

使用INSERT OR REPLACE时要非常小心，因为它会删除现有记录并插入新记录。如果需要更精细的控制，可以考虑使用UPDATE语句结合WHERE子句来手动检查和处理冲突。

UPDATE users
SET name = 'John Doe', email = 'john.doe@example.com'
WHERE id = 1;

INSERT INTO users (id, name, email)
SELECT 1, 'John Doe', 'john.doe@example.com'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = 1);

这种方法虽然稍微复杂一些，但可以更精确地控制数据的更新和插入过程。

通过这些方法，可以在SQLite中有效地处理插入和更新操作，同时避免常见的陷阱和问题。