SQLite不支持UPDATE FROM时高效更新一对多关联表的实现方法

优化方案 #

首先纠正原SQL的明显错误：子查询中写的SELECT notes不符合你的表结构定义，People表没有notes字段，需改为SELECT name，同时要增加LIMIT 1避免多个匹配行导致更新报错。

按以下步骤操作可大幅提升执行效率：

• 先创建必要的临时索引（更新完成后可自行删除，不影响业务）
  无索引是效率低的核心原因之一，先添加两个索引：

  -- 加速people_id非空记录的匹配更新
  CREATE INDEX idx_address_people_id ON Address(people_id);
  -- 若后续还需频繁做unique_identifier匹配可保留该索引，否则更新完即可删除
  CREATE INDEX idx_people_unique_id ON People(unique_identifier);
  

  注意：前模糊匹配%xxx%本身无法用到普通B树索引，如果你有大量模糊匹配需求，可以考虑给People的unique_identifier字段建FTS全文索引，匹配速度会提升数倍。

• 拆分UPDATE语句，避免OR导致的索引失效
  把原来用OR合并的逻辑拆成两次更新，第一次处理people_id有值的记录，第二次仅处理people_id为空的记录，避免全表扫描和无效匹配：

  -- 开启事务，批量提交比单条语句逐行执行效率高几十上百倍
  BEGIN TRANSACTION;
  
  -- 第一步：更新people_id非空的地址，纯等值匹配速度极快
  UPDATE Address
  SET name = (
    SELECT name FROM People WHERE People.id = Address.people_id LIMIT 1
  )
  WHERE people_id IS NOT NULL;
  
  -- 第二步：仅处理people_id为空的地址，无需扫描全表
  UPDATE Address
  SET name = (
    SELECT name FROM People WHERE Address.includes_unique_indentifier LIKE '%' || People.unique_identifier || '%' LIMIT 1
  )
  WHERE people_id IS NULL;
  
  -- 提交事务
  COMMIT;
  

• 可选优化：如果你的unique_identifier是固定规则的字符串（比如统一为18位身份证号），可以先把Address的includes_unique_indentifier里的对应标识提取出来存为单独字段，再做等值匹配，就能完全用上索引，效率比模糊匹配高一个数量级。

另外补充一个小问题：你定义Address表时把id设为VARCHAR(20)还加AUTOINCREMENT不符合SQLite语法，SQLite的AUTOINCREMENT只能用于INTEGER PRIMARY KEY字段，这里属于笔误。

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