SQLite拆分路径字符串查询文件父文件夹对应rowid的实现方法

完全可以用SQLite原生字符串函数实现需求，不需要依赖外部代码循环处理，性能远高于逐行查询更新。

单路径查询父ID #

如果需要查询单个指定路径的父文件夹rowid，直接使用以下查询语句即可：

-- 这里替换@target_path为你要查询的目标路径，替换your_table为实际表名
SELECT rowid FROM your_table
WHERE Path = (
    SELECT 
        SUBSTR(
            @target_path, 
            1, 
            LENGTH(@target_path) - INSTR(REVERSE(RTRIM(@target_path, '/')), '/')
        ) || '/'
);

函数逻辑说明：

• RTRIM(@target_path, '/')：统一移除路径末尾的斜杠，兼容文件、文件夹两种路径格式
• REVERSE(...)：反转处理后的路径，把原路径最后一个斜杠变成反转后字符串的第一个斜杠
• INSTR(..., '/')：获取反转后第一个斜杠的位置，等价于原路径最后一个斜杠到末尾的长度
• SUBSTR截取原路径到最后一个斜杠的位置，再拼接斜杠和库中存储的文件夹路径格式对齐

如果是Windows系统反斜杠路径，把语句中所有/替换为\即可。

批量更新全表Parent字段 #

你现有Python逐行循环更新的方案可以直接替换为单句SQL执行，性能提升显著，也避免了SQL注入风险：

-- 替换your_table为实际表名，根目录过滤条件根据你的实际根路径调整
UPDATE your_table
SET Parent = (
    SELECT rowid FROM your_table t2
    WHERE t2.Path = (
        SUBSTR(
            your_table.Path, 
            1, 
            LENGTH(your_table.Path) - INSTR(REVERSE(RTRIM(your_table.Path, '/')), '/')
        ) || '/'
    )
)
WHERE Path NOT IN ('/', 'grandparent/'); -- 排除不需要设置父ID的根目录

注意事项 #

• 给Path字段添加唯一索引，可大幅提升查询、更新的执行速度
• 原Python代码直接用format拼接SQL存在SQL注入风险，建议替换为参数化查询写法，示例：

# 错误写法
parent_query = '''SELECT rowid FROM {} WHERE Path = "{}"'''.format(self.table_name, parent_path_joined)
# 正确写法
parent_query = f'''SELECT rowid FROM {self.table_name} WHERE Path = ?'''
parent_paths = self.cur.execute(parent_query, (parent_path_joined,)).fetchone()

• 执行批量更新时可以最后再统一commit，不需要逐行commit，性能更高

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