Android中如何实现SQLite数据库的插入及更新操作？

前置条件 #

首先需要为userID字段添加唯一约束，可以直接将该字段设为主键，或者单独加UNIQUE约束，这是触发冲突判断的基础。建表示例如下：

CREATE TABLE IF NOT EXISTS user_local_data (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    -- 其他需要存储的字段示例
    user_name TEXT,
    user_config TEXT,
    last_update_time INTEGER
);

方案1：官方原生UPSERT（推荐，SQLite 3.24.0及以上版本支持） #

SQLite 从3.24.0开始支持标准的INSERT ... ON CONFLICT语法，原子操作无并发问题，单次请求即可完成逻辑，同时天然支持首次新增的场景：

INSERT INTO user_local_data (user_id, user_name, user_config, last_update_time)
VALUES (1001, "测试用户", '{"theme":"dark","notice":true}', 1718000000)
-- 触发user_id唯一约束冲突时执行更新
ON CONFLICT(user_id) DO UPDATE SET
    user_name = excluded.user_name,
    user_config = excluded.user_config,
    last_update_time = excluded.last_update_time;

说明：excluded是SQLite内置关键字，指代本次尝试插入、但触发冲突的行数据，不需要重复传入参数。

方案2：兼容旧版本SQLite的两步法 #

如果使用的SQLite版本低于3.24.0，可以先执行更新，根据影响行数判断是否需要执行插入：

# 以Python sqlite3库为例，其他语言逻辑同理
import sqlite3

db_conn = sqlite3.connect("your_database.db")
cursor = db_conn.cursor()

# 传入待保存的数据
target_user_id = 1001
save_data = ("测试用户", '{"theme":"dark","notice":true}', 1718000000, target_user_id)

# 第一步先执行更新
cursor.execute("""
UPDATE user_local_data 
SET user_name = ?, user_config = ?, last_update_time = ?
WHERE user_id = ?
""", save_data)

# 影响行数为0说明没有对应user_id，执行插入
if cursor.rowcount == 0:
    insert_data = (target_user_id, "测试用户", '{"theme":"dark","notice":true}', 1718000000)
    cursor.execute("""
    INSERT INTO user_local_data (user_id, user_name, user_config, last_update_time)
    VALUES (?, ?, ?, ?)
    """, insert_data)

db_conn.commit()
db_conn.close()

注意事项 #

• 两种方案都支持首次存储时自动执行插入，不需要额外做表空判断
• 优先使用方案1，原子操作不会出现并发场景下插入重复数据的问题

内容的提问来源于stack exchange，提问作者Malhotra