SQLite中基于files表批量复制scenes表并更新关联关系

我有两个关联表scenes和files，初始数据如下：

SCENES 表 #

id   created_at   updated_at   scene_id    
  --   -----------  ----------   ----------  
  1    2024-02-13   2024-03-05   vt-1105     
  2    2024-02-14   2024-03-06   KA-338      
  3    2024-02-15   2024-03-07   JU-182      
  4    2024-02-16   2024-03-08   PX-118      
  5    2024-02-17   2024-03-09   po-6339085  
  6    2024-02-18   2024-03-10   la-426    

FILES 表 #

id   created_at   updated_at   filename              scene_id  
  --   -----------  ----------   -------------------   --------  
  1    2024-03-03   2024-03-06   KA-338-A_180_LR.mp4   2         
  2    2024-03-03   2024-03-06   KA-338-B_180_LR.mp4   2         
  3    2024-03-03   2024-03-06   KA-338-C_180_LR.mp4   2         
  4    2024-03-04   2024-03-07   JU-182-A_180_LR.mp4   3         
  5    2024-03-04   2024-03-07   JU-182-B_180_LR.mp4   3         
  6    2024-03-04   2024-03-07   JU-182-C_180_LR.mp4   3         
  7    2024-03-04   2024-03-07   JU-182-D_180_LR.mp4   3         
  8    2024-03-05   2024-03-07   PX-118-A_180_LR.mp4   4         
  9    2024-03-05   2024-03-07   PX-118-B_180_LR.mp4   4         

需求目标 #

以场景JU-182为例，它关联了files表中scene_id为3的4个文件，需要完成：

1. 在scenes表生成4条该场景的重复记录
2. 将files表中对应4条记录的scene_id分别更新为新生成的场景ID

最终效果如下：

最终 SCENES 表 #

id   created_at   updated_at   scene_id    
  --   -----------  ----------   ----------  
  1    2024-02-13   2024-03-05   vt-1105     
  2    2024-02-14   2024-03-06   KA-338      
  3    2024-02-15   2024-03-07   JU-182      
  4    2024-02-16   2024-03-08   PX-118      
  5    2024-02-17   2024-03-09   po-6339085  
  6    2024-02-18   2024-03-10   la-426      
  7    2024-02-15   2024-03-07   JU-182      
  8    2024-02-15   2024-03-07   JU-182      
  9    2024-02-15   2024-03-07   JU-182      
  10   2024-02-15   2024-03-07   JU-182      

最终 FILES 表 #

id   created_at   updated_at   filename              scene_id  
  --   -----------  ----------   -------------------   --------  
  1    2024-03-03   2024-03-06   KA-338-A_180_LR.mp4   2         
  2    2024-03-03   2024-03-06   KA-338-B_180_LR.mp4   2         
  3    2024-03-03   2024-03-06   KA-338-C_180_LR.mp4   2         
  4    2024-03-04   2024-03-07   JU-182-A_180_LR.mp4   7         
  5    2024-03-04   2024-03-07   JU-182-B_180_LR.mp4   8         
  6    2024-03-04   2024-03-07   JU-182-C_180_LR.mp4   9         
  7    2024-03-04   2024-03-07   JU-182-D_180_LR.mp4   10        
  8    2024-03-05   2024-03-07   PX-118-A_180_LR.mp4   4         
  9    2024-03-05   2024-03-07   PX-118-B_180_LR.mp4   4         

已完成操作 #

我已创建临时表variables存储相关参数，并完成场景复制，将新场景ID存入variables表：

CREATE TEMP TABLE variables (
    scene_id TEXT,
    scene_index INT,
    files_total INT,
    file_ids TEXT,
    scene_newIDs TEXT -- 新增字段存储新生成的场景ID
);
INSERT INTO variables VALUES (
    'JU-182', -- 手动替换为目标场景ID
    NULL,
    NULL,
    NULL,
    NULL
); 
UPDATE variables
    SET scene_index = (
    SELECT id FROM scenes WHERE scene_id = (
            SELECT scene_id FROM variables
        )
    );  
UPDATE variables
    SET files_total = (
        SELECT COUNT(*) FROM files WHERE scene_id = (
            SELECT scene_index FROM variables
        )
    );
UPDATE variables
    SET file_ids = (
        SELECT GROUP_CONCAT(id, ',') FROM files WHERE scene_id = (
            SELECT scene_index FROM variables
        )
    );

此时variables表数据：

scene_id   scene_index   scene_newIDs   files_total   file_ids  
  --------   -----------   ------------   -----------   --------  
  JU-182     3             7,8,9,10       4             4,5,6,7   

执行场景复制SQL：

WITH cte(x) AS (
    SELECT 1 
    UNION ALL 
    SELECT x + 1 
    FROM cte WHERE x < (SELECT files_total FROM variables)
)
INSERT INTO scenes (created_at, updated_at, scene_id)
    SELECT created_at, updated_at, scene_id
    FROM scenes JOIN cte
    WHERE id = (SELECT scene_index FROM variables);

当前卡点 #

需要将files表中id为4、5、6、7的记录的scene_id分别更新为7、8、9、10，求实现方法。

可以通过拆分ID字符串为有序列表，再按序号关联匹配的方式实现精准更新，具体步骤如下：

1. 确保新场景ID已存入临时表 #

如果还未将新生成的场景ID写入variables表，先执行这条语句：

UPDATE variables
SET scene_newIDs = (
    SELECT GROUP_CONCAT(id, ',') 
    FROM scenes 
    WHERE scene_id = (SELECT scene_id FROM variables) 
      AND id > (SELECT scene_index FROM variables)
    ORDER BY id ASC
);

2. 拆分ID并关联更新 #

使用CTE将逗号分隔的file_ids和scene_newIDs拆分为带序号的行，再通过序号匹配完成更新：

WITH 
-- 拆分文件ID为带序号的行
split_files AS (
    SELECT 
        SUBSTR(value, 1) AS file_id,
        ROW_NUMBER() OVER(ORDER BY instr((SELECT file_ids FROM variables), value)) AS rn
    FROM json_each('["' || REPLACE((SELECT file_ids FROM variables), ',', '","') || '"]')
),
-- 拆分新场景ID为带序号的行
split_scenes AS (
    SELECT 
        SUBSTR(value, 1) AS new_scene_id,
        ROW_NUMBER() OVER(ORDER BY instr((SELECT scene_newIDs FROM variables), value)) AS rn
    FROM json_each('["' || REPLACE((SELECT scene_newIDs FROM variables), ',', '","') || '"]')
)
-- 按序号关联更新
UPDATE files f
SET scene_id = (SELECT new_scene_id FROM split_scenes s WHERE s.rn = (SELECT rn FROM split_files sf WHERE sf.file_id = f.id))
WHERE f.id IN (SELECT file_id FROM split_files);

兼容说明 #

如果你的数据库不支持json_each，可以改用递归CTE拆分字符串，以file_ids为例：

split_files AS (
    SELECT 
        CAST(SUBSTR(file_ids, 1, INSTR(file_ids, ',')-1) AS INT) AS file_id,
        SUBSTR(file_ids, INSTR(file_ids, ',')+1) AS remaining,
        1 AS rn
    FROM variables
    UNION ALL
    SELECT 
        CAST(SUBSTR(remaining, 1, INSTR(remaining, ',')-1) AS INT),
        SUBSTR(remaining, INSTR(remaining, ',')+1),
        rn + 1
    FROM split_files
    WHERE remaining != ''
)

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