SQLite中使用INSERT OR IGNORE时如何获取已存在数据的ID？

由于t列设置了UNIQUE约束，使用INSERT OR IGNORE插入重复值时，lastrowid只会返回上一次成功插入的ID，而非已存在数据的对应ID，示例如下：

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE data(id INTEGER PRIMARY KEY AUTOINCREMENT, t TEXT UNIQUE);")
c = conn.cursor()
c.execute("INSERT OR IGNORE INTO data(t) VALUES (?)", ("foo", ))
print(c.lastrowid)  # 1
c.execute("INSERT OR IGNORE INTO data(t) VALUES (?)", ("bar", ))
print(c.lastrowid)  # 2
c.execute("INSERT OR IGNORE INTO data(t) VALUES (?)", ("foo", ))
print(c.lastrowid)  # 2，期望获取foo对应的已存在ID：1

核心问题：

• 如何在插入重复值时，获取已存在数据的ID，而非上一次成功插入的ID？
• 能否不通过额外的SELECT语句实现（避免增加耗时）？

尝试过的无效方法：使用INSERT OR IGNORE ... RETURNING *
这种方式下，当插入触发唯一约束时，RETURNING不会返回任何结果，lastrowid依然保持上一次成功插入的ID，无法得到已存在数据的ID：

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE data(id INTEGER PRIMARY KEY AUTOINCREMENT, t TEXT UNIQUE);")
c = conn.cursor()
for r in c.execute("INSERT OR IGNORE INTO data(t) VALUES (?) RETURNING *;", ("foo", )):
    print(r)  # 输出(1, 'foo')
print(c.lastrowid)  # 1
for r in c.execute("INSERT OR IGNORE INTO data(t) VALUES (?) RETURNING *;", ("bar", )):
    print(r)  # 输出(2, 'bar')
print(c.lastrowid)  # 2
for r in c.execute("INSERT OR IGNORE INTO data(t) VALUES (?) RETURNING *;", ("foo", )):
    print(r)  # 无输出
print(c.lastrowid)  # 2，仍无法获取foo的ID 1

可行解决方案：用INSERT ... ON CONFLICT替代INSERT OR IGNORE #

利用SQLite的ON CONFLICT子句，在触发唯一约束时执行一个无实际修改的更新操作，同时通过RETURNING返回目标ID，无需额外查询：

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE data(id INTEGER PRIMARY KEY AUTOINCREMENT, t TEXT UNIQUE);")
c = conn.cursor()

# 首次插入foo
result = c.execute("INSERT INTO data(t) VALUES (?) ON CONFLICT(t) DO UPDATE SET t = excluded.t RETURNING id;", ("foo", )).fetchone()
print(result[0])  # 输出1
print(c.lastrowid)  # 输出1

# 插入bar
result = c.execute("INSERT INTO data(t) VALUES (?) ON CONFLICT(t) DO UPDATE SET t = excluded.t RETURNING id;", ("bar", )).fetchone()
print(result[0])  # 输出2
print(c.lastrowid)  # 输出2

# 重复插入foo
result = c.execute("INSERT INTO data(t) VALUES (?) ON CONFLICT(t) DO UPDATE SET t = excluded.t RETURNING id;", ("foo", )).fetchone()
print(result[0])  # 输出1，成功获取已存在的ID
print(c.lastrowid)  # 输出1，lastrowid同步更新为目标ID

原理说明 #

• ON CONFLICT(t)：指定当t列触发唯一约束冲突时执行后续逻辑
• DO UPDATE SET t = excluded.t：执行一个无意义的更新（excluded代表待插入的行，这里t值和现有行完全一致，不会修改任何数据）
• RETURNING id：无论插入成功还是冲突触发更新，都会返回对应的id值，直接拿到目标ID，无需额外查询

注意：该方法要求SQLite版本在3.24.0及以上（ON CONFLICT子句在该版本正式引入）。

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