SQLite Upsert Update|极客教程

SQLite Upsert Update

1. 什么是 Upsert？

在数据库操作中，upsert 可以理解为一种“更新或插入”的操作。当我们执行 upsert 操作时，如果数据库中已经存在相同的记录，则更新这条记录的值；如果数据库中不存在相同的记录，则插入一条新的记录。

在 SQLite 中，通常使用 INSERT OR REPLACE 或者 INSERT ON CONFLICT UPDATE 语句来实现 upsert 操作。这两种方式本质上是一样的，只是语法稍有不同。

2. INSERT OR REPLACE

INSERT OR REPLACE 语句会先尝试插入新记录，如果数据库中已存在相同的唯一键（UNIQUE key constraint），则先删除旧记录，再插入新记录。

INSERT OR REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

示例代码：

CREATE TABLE IF NOT EXISTS users(
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);

INSERT OR REPLACE INTO users (id, name, age) VALUES (1, 'Alice', 25);
INSERT OR REPLACE INTO users (id, name, age) VALUES (2, 'Bob', 30);

运行以上代码后，如果数据库中已存在 id 为1的记录，则会更新该记录中的 name 和 age 字段；如果数据库中不存在 id 为2的记录，则会插入一条新记录。

3. INSERT ON CONFLICT UPDATE

INSERT ON CONFLICT UPDATE 语句是 SQLite 特有的语法，在插入数据时指定当发生唯一性冲突（UNIQUE constraint violation）时的处理方式，可以选择更新已存在记录的值。

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
ON CONFLICT (conflict_column) DO UPDATE SET column1 = new_value1, column2 = new_value2, ...;

示例代码：

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25) ON CONFLICT (id) DO UPDATE SET name = 'Alice Smith', age = 26;
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 35) ON CONFLICT (id) DO UPDATE SET name = 'Charlie Brown', age = 36;

运行以上代码后，如果数据库中已存在 id 为1的记录，则会更新该记录中的 name 和 age 字段；如果数据库中不存在 id 为3的记录，则会插入一条新记录。

4. 使用 UPSERT 操作的注意事项

在使用 UPSERT 操作时，需要注意以下几点：

确保数据库表中存在合适的唯一键（UNIQUE key constraint）或主键（PRIMARY KEY constraint），以确保可以正确识别重复记录；
注意处理并发情况，避免多个操作同时对同一条记录进行更新；
在使用 INSERT ON CONFLICT UPDATE 语句时，注意设置合适的冲突规则和更新操作，以确保数据一致性。