SQLite数据库路径列最佳匹配查询方案求助

核心思路很直接：先筛选出所有是查询路径前缀的存储路径，再从中挑出最长的那条——最长的前缀就是最精确的匹配项。

假设你的表名为file_paths，存储路径的列名为path，可以用下面的SQL语句实现需求：

SELECT path
FROM file_paths
WHERE '/my/folder/foo/images/png/myimage.png' LIKE path || '%'
ORDER BY LENGTH(path) DESC
LIMIT 1;

语句说明 #

• WHERE 子句：通过LIKE判断查询路径是否以存储路径为前缀（path || '%'将存储路径拼接为前缀通配符）
• ORDER BY LENGTH(path) DESC：按路径长度从长到短排序，保证最长的匹配项排在首位
• LIMIT 1：只取第一条结果，也就是我们要的最佳匹配

如果查询路径是/my/folder/foo/bar/pdf/mypdf.pdf，替换WHERE中的路径即可：

SELECT path
FROM file_paths
WHERE '/my/folder/foo/bar/pdf/mypdf.pdf' LIKE path || '%'
ORDER BY LENGTH(path) DESC
LIMIT 1;

额外优化（避免部分目录名误匹配） #

如果担心出现“存储路径是/my/folder/fo，却被误匹配为/my/folder/foo前缀”的情况，可以调整WHERE条件，确保匹配的是完整目录：

SELECT path
FROM file_paths
WHERE '/my/folder/foo/images/png/myimage.png' LIKE path || '/%' 
   OR '/my/folder/foo/images/png/myimage.png' = path
ORDER BY LENGTH(path) DESC
LIMIT 1;

这个条件要求存储路径要么和查询路径完全一致，要么查询路径以存储路径+/开头，彻底避免部分目录名的误匹配问题。

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