如何在MySQL查询中对指定父路径下的子路径仅保留单条记录？

嘿，这个需求完全可以在MySQL里直接实现！核心思路是提取目标路径下的第一个子项（不管是目录还是文件），然后按这个子项分组，每组只保留一条记录。下面给你两种适用不同MySQL版本的方案，都是参数化写法，和你现有的PDO代码兼容，不会有SQL注入风险。

方案一：适用于MySQL 8.0+（窗口函数，更灵活） #

窗口函数ROW_NUMBER()可以帮你给每个子项组的记录编号，然后只取编号为1的那条，还能自定义排序规则（比如按路径字典序、创建时间等）。

$dir = 'Volumes/animals/big';
$sql = "WITH ranked_paths AS (
    SELECT 
        path,
        ROW_NUMBER() OVER (
            PARTITION BY REGEXP_SUBSTR(path, CONCAT(TRIM(TRAILING '/' FROM ?), '/([^/]+)'), 1, 1, 'c', 1)
            ORDER BY path -- 可以换成你需要的排序字段，比如`created_at DESC`取最新记录
        ) AS rn
    FROM files
    WHERE id = ? 
      AND path LIKE CONCAT(TRIM(TRAILING '/' FROM ?), '%')
)
SELECT path
FROM ranked_paths
WHERE rn = 1";

$q = $pdo->prepare($sql);
$q->execute([$dir, $id, $dir]);
$array = $q->fetchAll(PDO::FETCH_ASSOC);

代码解释： #

• TRIM(TRAILING '/' FROM ?)：统一处理$dir末尾是否带/的情况，避免路径匹配出错。
• REGEXP_SUBSTR(...)：用正则提取$dir/之后的第一个子项（比如horses、elephant、map.jpg），作为分组依据。
• PARTITION BY ...：按提取的子项分组，给每组的记录编号。
• ORDER BY path：控制每组取哪条记录，这里取字典序最小的，你可以根据需求修改。

方案二：适用于MySQL 5.x版本（字符串函数+GROUP BY） #

如果你的MySQL版本低于8.0，用字符串函数提取子项后分组，配合聚合函数取每组的一条记录即可。

$dir = 'Volumes/animals/big';
$sql = "SELECT MIN(path) AS path
FROM files
WHERE id = ? 
  AND path LIKE CONCAT(TRIM(TRAILING '/' FROM ?), '%')
GROUP BY SUBSTRING_INDEX(
    SUBSTRING(path, LENGTH(TRIM(TRAILING '/' FROM ?)) + 1),
    '/',
    1
)";

$q = $pdo->prepare($sql);
$q->execute([$id, $dir, $dir]);
$array = $q->fetchAll(PDO::FETCH_ASSOC);

代码解释： #

• SUBSTRING(path, LENGTH(...) +1)：提取$dir之后的路径部分（比如/horses/shetland/amy.jpg）。
• SUBSTRING_INDEX(..., '/', 1)：从提取的路径中取出第一个/之前的内容，得到第一个子项。
• GROUP BY ...：按子项分组，MIN(path)取每组字典序最小的记录（换成MAX(path)可以取最大的）。

两种方案都能得到你期望的结果：每个Volumes/animals/big/下的直接子项（目录或文件）仅保留一条记录。

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