SQLite中实现UPSERT并获取匹配行ID的高效方案

数据老陈

2026-7-28

问题

我正尝试将CSV文件中的数据导入到规范化的数据库结构中。简化示例如下：

存在包含人员及其住址（门牌号、街道、城市）的列表，多人可同住一处但每人仅有一个住址。
已创建临时表存储CSV导入的数据：

CREATE TABLE temp (name TEXT, houseNo INTEGER, street TEXT, city TEXT);
INSERT INTO temp VALUES('Mr Benn',52,'Festive Road','London');
INSERT INTO temp VALUES('Prime Minister',10,'Downing Street','London');
INSERT INTO temp VALUES('Mrs Benn',52,'Festive Road','London');
INSERT INTO temp VALUES('Baby Benn',52,'Festive Road','London');
INSERT INTO temp VALUES('Reggie Perrin',12,'Coleridge Close','London');
INSERT INTO temp VALUES('Larry The Cat',10,'Downing Street','London');

需要将数据导入以下三张规范化表：

CREATE TABLE people(name TEXT PRIMARY KEY);
CREATE TABLE addresses(id INTEGER PRIMARY KEY, houseNo INTEGER, street TEXT, city TEXT,UNIQUE(houseNo,street,city) );
CREATE TABLE homes(id INTEGER PRIMARY KEY, name TEXT NOT NULL, addressId INTEGER NOT NULL,FOREIGN KEY (name) REFERENCES people(name), FOREIGN KEY (addressId) REFERENCES addresses(id));

针对临时表中的每一行，需完成以下操作：

将name插入people表；
对addresses表执行UPSERT操作，并获取对应的id（无论该行是新增还是已存在）；
使用该id在homes表中插入一行数据。

我已编写如下SQL语句实现需求：

INSERT INTO people SELECT name FROM temp;
INSERT INTO addresses(houseNo,street,city) SELECT houseNo,street,city FROM temp WHERE TRUE ON CONFLICT DO NOTHING;
INSERT INTO homes(name,addressId) SELECT t.name,a.id FROM addresses a, temp t WHERE a.houseNo = t.houseNo AND a.street = t.street AND a.city=t.city;
SELECT * FROM homes

该语句可得到预期结果：

id      name    addressId
--      ----    ---------
1       Mr Benn         1
2       Prime Minister  2
3       Mrs Benn        1
4       Baby Benn       1
5       Reggie Perrin   3
6       Larry The Cat   2

但此方案需要遍历临时表3次，而我的实际数据集更大且包含更多列，效率较低。因为在步骤2中已经能获取步骤3所需的id，因此想询问：在SQLite中是否存在更高效的实现方式？

高效实现方案

在SQLite 3.35.0及以上版本中，可以利用RETURNING子句结合公共表表达式（CTE），将遍历临时表的次数减少到2次，同时完成所有操作，大幅提升大数据集下的导入效率。

具体SQL代码如下：

WITH processed_temp AS (
    -- 对临时表中的地址去重，避免重复执行UPSERT操作
    SELECT DISTINCT houseNo, street, city FROM temp
),
address_ids AS (
    -- 对地址执行UPSERT，无论新增还是已存在，都返回对应的ID和地址信息
    INSERT INTO addresses(houseNo, street, city)
    SELECT houseNo, street, city FROM processed_temp
    ON CONFLICT(houseNo, street, city) DO UPDATE SET houseNo = excluded.houseNo
    RETURNING id, houseNo, street, city
),
inserted_people AS (
    -- 插入人员表，去重并处理主键冲突
    INSERT INTO people(name)
    SELECT DISTINCT name FROM temp
    ON CONFLICT(name) DO NOTHING
)
-- 关联临时表和地址ID，插入到关联表homes中
INSERT INTO homes(name, addressId)
SELECT t.name, a.id
FROM temp t
JOIN address_ids a 
  ON t.houseNo = a.houseNo 
  AND t.street = a.street 
  AND t.city = a.city;

-- 验证结果
SELECT * FROM homes;

方案优势

减少遍历次数：仅需遍历临时表两次（一次地址去重、一次关联插入），相比原方案的三次遍历，在大数据集下能显著降低IO开销。
直接获取地址ID：通过RETURNING子句在UPSERT地址后直接拿到对应的ID，无需后续再次查询addresses表匹配地址。
逻辑更健壮：对人员表插入时增加了去重和冲突处理，避免临时表中重复姓名导致的主键冲突报错。

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

SQLite中实现UPSERT并获取匹配行ID的高效方案

方案优势 #

方案优势