PowerShell中SQL MERGE无法删除SQL内目录不存在的孤立行求助

你的核心问题在于当前脚本每次只传入单个文件到MERGE语句中，导致WHEN NOT MATCHED BY SOURCE的逻辑完全失效——因为此时SOURCE只有当前这一个文件，它只会检查数据库里有没有这个文件（没有就插入），但根本没法对比"数据库里存在但目录中不存在"的所有文件，自然删不掉孤立行。

要实现同步目录与SQL表（插入新文件、删除目录中不存在的旧文件），你需要把整个目录的文件集合作为SOURCE和数据库表做对比，而不是循环处理单个文件。下面是修改后的完整方案：

关键修改思路 #

1. 一次性收集所有目录文件的完整数据，避免循环逐个处理
2. 构建包含所有文件数据的MERGE语句，让SOURCE是整个目录的文件集合
3. 利用WHEN NOT MATCHED BY SOURCE精准删除数据库中存在但目录里没有的行
4. 修复原脚本的SQL注入风险（字符串拼接参数极易引发语法错误和安全问题）

修改后的完整脚本 #

Add-Type -AssemblyName "System.Web"

function Get-FriendlySize {
    param($Bytes)
    $sizes='Bytes,KB,MB,GB,TB,PB,EB,ZB' -split ','
    for($i=0; ($Bytes -ge 1kb) -and ($i -lt $sizes.Count); $i++) {$Bytes/=1kb}
    $N=2; if($i -eq 0) {$N=0}
    "{0:N$($N)} {1}" -f $Bytes, $sizes[$i]
}

# 数据库连接参数
$sqlServer='localhost'
$params = @{
    'Server' = 'localhost'
    'Database' = 'kentico'
}

# 目标目录路径（统一管理，避免硬编码）
$targetPath = "C:\inetpub\wwwroot\Kentico11\CMS\t\media\test\"

# 一次性收集所有文件的完整数据
$fileList = Get-ChildItem -Path $targetPath -Recurse -File | Select-Object -Property `
    @{Label='FileName'; Expression={$_.BaseName}},
    @{Label='FileTitle'; Expression={$_.BaseName}},
    @{Name='FileDescription'; Expression={"bob"}},
    @{Label='FileExtension'; Expression={$_.Extension.TrimStart('.')}}, # 去掉扩展名前的点号，符合数据库存储规范
    @{Name='FileMimeType'; Expression={[System.Web.MimeMapping]::GetMimeMapping($_.FullName)}},
    @{Label='FilePath'; Expression={($_.FullName.Remove(0, $targetPath.Length)).Replace('\', '/')}}, # 动态计算相对路径，避免硬编码长度
    @{Name='FileSize'; Expression={Get-FriendlySize $_.Length}}, # 使用真实文件大小，替换固定值
    @{Name='FileImageWidth'; Expression={$null}},
    @{Name='FileImageHeight'; Expression={$null}},
    @{Name='FileGUID'; Expression={[guid]::NewGuid()}},
    @{Name='FileLibraryID'; Expression={"15"}},
    @{Name='FileSiteID'; Expression={"3"}},
    @{Name='FileCreatedByUserID'; Expression={"53"}},
    @{Label='FileCreatedWhen'; Expression={$_.CreationTime.ToString('yyyy-MM-dd HH:mm:ss')}}, # 标准化SQL兼容的日期格式
    @{Name='FileModifiedByUserID'; Expression={"53"}},
    @{Label='FileModifiedWhen'; Expression={$_.LastWriteTime.ToString('yyyy-MM-dd HH:mm:ss')}},
    @{Name='FileCustomData'; Expression={$null}},
    @{Label='FileDate'; Expression={
        # 安全处理文件名日期截取，避免长度不足报错
        if($_.BaseName.Length -ge 13){ $_.BaseName.Substring($_.BaseName.Length -13,13) } else { $null }
    }}

# 构建MERGE语句的SOURCE数据部分（转义单引号避免SQL语法错误）
$sourceValues = $fileList | ForEach-Object {
    $escapedFileName = $_.FileName.Replace("'", "''")
    $escapedFileTitle = $_.FileTitle.Replace("'", "''")
    $escapedFileDescription = $_.FileDescription.Replace("'", "''")
    $escapedFilePath = $_.FilePath.Replace("'", "''")
    
    "('$escapedFileName','$escapedFileTitle','$escapedFileDescription','$($_.FileExtension)','$($_.FileMimeType)','$escapedFilePath','$($_.FileSize)',NULL,NULL,'$($_.FileGUID)','$($_.FileLibraryID)','$($_.FileSiteID)','$($_.FileCreatedByUserID)','$($_.FileCreatedWhen)','$($_.FileModifiedByUserID)','$($_.FileModifiedWhen)',NULL,'$($_.FileDate)')"
} -Join ",`n"

# 完整的MERGE同步语句
$mergeQuery = @"
MERGE dbo.Media_File AS T
USING (
    SELECT 
        FileName, FileTitle, FileDescription, FileExtension, FileMimeType,
        FilePath, FileSize, FileImageWidth, FileImageHeight, FileGUID,
        FileLibraryID, FileSiteID, FileCreatedByUserID, FileCreatedWhen,
        FileModifiedByUserID, FileModifiedWhen, FileCustomData, FileDate
    FROM (VALUES
        $sourceValues
    ) AS S(FileName, FileTitle, FileDescription, FileExtension, FileMimeType, FilePath, FileSize, FileImageWidth, FileImageHeight, FileGUID, FileLibraryID, FileSiteID, FileCreatedByUserID, FileCreatedWhen, FileModifiedByUserID, FileModifiedWhen, FileCustomData, FileDate)
) AS S
ON S.FileName = T.FileName
WHEN NOT MATCHED BY TARGET THEN
    INSERT (FileName,FileTitle,FileDescription,FileExtension,FileMimeType,FilePath,FileSize,FileImageWidth,FileImageHeight,FileGUID,FileLibraryID,FileSiteID,FileCreatedByUserID,FileCreatedWhen,FileModifiedByUserID,FileModifiedWhen,FileCustomData,FileDate)
    VALUES (S.FileName,S.FileTitle,S.FileDescription,S.FileExtension,S.FileMimeType,S.FilePath,S.FileSize,S.FileImageWidth,S.FileImageHeight,S.FileGUID,S.FileLibraryID,S.FileSiteID,S.FileCreatedByUserID,S.FileCreatedWhen,S.FileModifiedByUserID,S.FileModifiedWhen,S.FileCustomData,CAST(S.FileDate AS datetime))
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
"@

# 执行同步操作
Invoke-Sqlcmd @params -Query $mergeQuery

# 验证同步结果
Invoke-Sqlcmd @params -Query "SELECT * FROM Media_File" | Format-Table -AutoSize

核心修改说明 #

1. 批量处理逻辑：一次性收集所有文件数据，构建包含全量文件的SOURCE集合，让MERGE能对比整个目录与数据库的差异
2. 正确的删除逻辑：WHEN NOT MATCHED BY SOURCE现在会精准匹配数据库中存在但目录里已删除的文件，执行删除操作
3. 鲁棒性优化：动态计算相对路径、标准化日期格式、安全处理文件名截取，避免硬编码和格式错误
4. 安全防护：转义字符串中的单引号，避免SQL语法错误和注入风险（若需更高安全性，建议使用SQL表值参数）

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