MySQL查询包含全部指定document_id的folder_id方法

现有folder（文件夹）、document（文档）两个实体，文档归属于文件夹，二者通过多对多中间表folder_document存储关联关系，表结构与示例数据如下：

需求为查询所有包含传入数组中全部document_id的folder_id：

• 示例：传入[11, 12]时返回[2]，它是唯一同时包含两个目标文档的文件夹；即使文件夹包含数组外的文档（如示例中的13），也符合返回条件
• 当前数据规模：文档总量约1000条，文件夹总量约300万条

已尝试的SQL写法如下，不确定大数据量下是否正确、高效：

select folder_id 
from folder_document 
where document_id in (11, 12)
group by folder_id  
having count(document_id) = 2 -- 数值等于传入数组的长度

补充要求：传入的文档ID数组最终会拼接为SQL中IN子句的参数列表（如传入[11,12]对应IN子句的(11,12)），需要实现通用PHP方法：传入文档ID数组即可自动构建正确SQL，执行后返回符合要求的文件夹ID集合，方法框架如下：

public function foo(array $arrayOfDocumentIds) 
{
  // 可通过implode(', ', $arrayOfDocumentIds)等方式将数组转为SQL参数
  $query = // 需补全查询构建逻辑

  $idsOfFoldersThatContainAllTheDocuments = $databaseService->execute($query);

  return $idsOfFoldersThatContainAllTheDocuments;
}

原有SQL正确性判断 #

你写的SQL逻辑完全成立：先通过IN条件过滤出所有关联了目标文档的中间表记录，按folder_id分组后，统计每组内的document_id数量，数量等于传入文档总数的分组，就是关联了全部目标文档的文件夹。

正确性前提：中间表folder_document需要对(folder_id, document_id)建立联合唯一约束，避免出现同一文件夹重复关联同一文档的脏数据，否则count统计值会偏大，导致结果错误。如果无法保证数据绝对干净，可以把count(document_id)改成count(DISTINCT document_id)做双重保险，性能损耗极小。

性能优化说明 #

针对300万量级的文件夹数据，只要索引配置正确，这个写法的执行效率非常高，是该场景下的最优方案之一：

• 必须给中间表建立(document_id, folder_id)的联合索引，IN查询会直接走索引定位关联记录，不需要全表扫描
• 分组统计阶段依托索引的有序性，计算成本极低，即使传入的文档ID数量较多，也不会出现明显的性能下滑
• 不需要额外写多表关联、嵌套子查询，逻辑简洁，执行计划稳定

PHP方法实现 #

public function foo(array $arrayOfDocumentIds) 
{
    // 传入空数组直接返回空结果，避免生成语法错误的SQL
    if (empty($arrayOfDocumentIds)) {
        return [];
    }
    // 所有ID转为整数，从参数层面杜绝SQL注入风险
    $documentIds = array_map('intval', $arrayOfDocumentIds);
    // 对传入ID去重，避免重复参数导致count统计值不准
    $documentIds = array_unique($documentIds);
    $matchCount = count($documentIds);
    $idStr = implode(',', $documentIds);

    // 构建最终查询SQL
    $query = "SELECT folder_id
              FROM folder_document
              WHERE document_id IN ({$idStr})
              GROUP BY folder_id
              HAVING COUNT(DISTINCT document_id) = {$matchCount}";

    return $databaseService->execute($query);
}

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