setName('attachment:scan')
->addOption('delete', null, Option::VALUE_NONE, '删除孤儿附件（记录+文件+COS对象）')
->setDescription('扫描无业务引用的孤儿附件');
}
/**
* 命令执行
*/
protected function execute(Input $input, Output $output)
{
$config = Config::get('database');
$database = $config['database'];
$prefix = $config['prefix'];
// 1. 收集业务表所有文本类列（排除附件表自身）
$columns = Db::query(
"SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS "
. "WHERE TABLE_SCHEMA = ? AND DATA_TYPE IN ('varchar','char','text','tinytext','mediumtext','longtext') "
. "AND TABLE_NAME NOT IN (?, CONCAT(?, 'attachment'))",
[$database, $database . '.' . $prefix . 'attachment', $prefix]
);
if (!$columns) {
$output->writeln('未找到业务表列');
return;
}
// 2. 遍历附件记录，逐列检查 url 引用（每列一次查询，OR 组合全部 url）
$attachments = Db::name('attachment')->field('id,url,storage,filename,uploadtime')->select();
$urls = array_column($attachments, 'url');
$referenced = []; // 有引用的 url 集合
foreach ($columns as $col) {
$table = $col['TABLE_NAME'];
$field = $col['COLUMN_NAME'];
$wheres = [];
$bind = [];
foreach ($urls as $i => $url) {
$wheres[] = "`{$field}` LIKE :u{$i}";
$bind["u{$i}"] = '%' . addcslashes($url, '%_') . '%';
}
$sql = "SELECT `{$field}` AS v FROM `{$table}` WHERE " . implode(' OR ', $wheres) . " LIMIT 500";
try {
$rows = Db::query($sql, $bind);
} catch (\Exception $e) {
continue; // 个别列类型不兼容时跳过
}
foreach ($rows as $row) {
$v = (string)$row['v'];
foreach ($urls as $url) {
if ($url !== '' && strpos($v, $url) !== false) {
$referenced[$url] = true;
}
}
}
}
// 3. 输出孤儿清单
$orphans = array_values(array_filter($attachments, function ($a) use ($referenced) {
return empty($referenced[$a['url']]);
}));
if (!$orphans) {
$output->writeln('扫描完成：所有附件均有业务引用，无孤儿文件');
return;
}
$output->writeln(sprintf('扫描完成：共 %d 条附件，孤儿 %d 条：', count($attachments), count($orphans)));
foreach ($orphans as $o) {
$output->writeln(sprintf(' [%d] %s (%s) %s', $o['id'], $o['url'], $o['storage'], date('Y-m-d', $o['uploadtime'])));
}
// 4. 可选删除：手动清理本地文件 / COS 对象 / 记录
if ($input->getOption('delete')) {
Loader::addNamespace('Qcloud\Cos', ROOT_PATH . 'addons/cos/library/Qcloud/Cos/');
$cosConfig = get_addon_config('cos');
$client = null;
if ($cosConfig && !empty($cosConfig['cdnurl'])) {
$client = new \Qcloud\Cos\Client([
'region' => $cosConfig['region'],
'schema' => 'https',
'credentials' => ['secretId' => $cosConfig['secretId'], 'secretKey' => $cosConfig['secretKey']],
]);
}
foreach ($orphans as $o) {
$id = $o['id'];
// 同 URL 成对记录一起删
$pairs = Db::name('attachment')->where('url', $o['url'])->select();
foreach ($pairs as $pair) {
if ($pair['storage'] === 'local') {
$file = ROOT_PATH . 'public' . $pair['url'];
if (is_file($file)) {
@unlink($file);
}
} elseif ($pair['storage'] === 'cos' && $client) {
try {
$client->deleteObject(['Bucket' => $cosConfig['bucket'], 'Key' => ltrim($pair['url'], '/')]);
} catch (\Exception $e) {
$output->writeln('COS删除失败: ' . $pair['url'] . ' ' . $e->getMessage() . '');
}
}
Db::name('attachment')->where('id', $pair['id'])->delete();
}
$output->writeln('已删除: ' . $o['url'] . '');
}
}
}
}