MySQL：如何无需逐个查询检查file表ID是否被其他表引用？

针对你的需求，结合Nest.js + TypeORM技术栈，这里提供几种无需硬编码逐个查询业务表的规范方案：

方案1：利用数据库系统表动态生成无引用File查询 #

所有关联file表的业务表都会存在指向file.id的外键约束，我们可以直接查询数据库的系统元数据表，获取所有关联的表和字段，再动态构建查询找出未被引用的file。

以PostgreSQL为例，先查询所有关联file的外键信息：

SELECT 
  tc.table_name AS referenced_table,
  kcu.column_name AS referenced_column
FROM 
  information_schema.table_constraints AS tc
JOIN 
  information_schema.key_column_usage AS kcu
ON 
  tc.constraint_name = kcu.constraint_name
WHERE 
  tc.constraint_type = 'FOREIGN KEY'
  AND kcu.referenced_table_name = 'file'
  AND kcu.referenced_column_name = 'id';

在TypeORM中，基于这个查询结果动态构建左连接查询：

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { File } from './entities/file.entity';

@Injectable()
export class FileCleanupService {
  constructor(
    @InjectRepository(File)
    private fileRepository: Repository<File>,
  ) {}

  async findUnusedFiles(): Promise<File[]> {
    // 获取所有关联file的表和字段
    const foreignKeyInfo = await this.fileRepository.query(`
      SELECT 
        tc.table_name AS table_name,
        kcu.column_name AS column_name
      FROM 
        information_schema.table_constraints AS tc
      JOIN 
        information_schema.key_column_usage AS kcu
      ON 
        tc.constraint_name = kcu.constraint_name
      WHERE 
        tc.constraint_type = 'FOREIGN KEY'
        AND kcu.referenced_table_name = 'file'
        AND kcu.referenced_column_name = 'id';
    `);

    const queryBuilder = this.fileRepository.createQueryBuilder('file');

    // 对每个关联表做左连接
    foreignKeyInfo.forEach(({ table_name, column_name }) => {
      queryBuilder.leftJoin(table_name, table_name, `${table_name}.${column_name} = file.id`);
    });

    // 筛选所有关联表无匹配记录的file
    const whereConditions = foreignKeyInfo.map(({ table_name }) => `${table_name}.id IS NULL`);
    queryBuilder.where(whereConditions.join(' AND '));

    return queryBuilder.getMany();
  }
}

注意：不同数据库的系统表结构略有差异，比如MySQL的查询语句需要调整，核心逻辑一致。

方案2：利用TypeORM实体元数据动态生成查询 #

TypeORM会在运行时保存所有实体的关联元数据，我们可以通过getMetadataArgsStorage()获取所有关联到File实体的业务实体和字段，动态构建查询。

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, getMetadataArgsStorage } from 'typeorm';
import { File } from './entities/file.entity';

@Injectable()
export class FileCleanupService {
  constructor(
    @InjectRepository(File)
    private fileRepository: Repository<File>,
  ) {}

  async findUnusedFiles(): Promise<File[]> {
    const metadataStorage = getMetadataArgsStorage();
    // 获取所有关联到File实体的ManyToOne关联（业务表的image字段属于此类）
    const fileRelations = metadataStorage.relations.filter(
      (relation) => relation.type === 'many-to-one' && relation.target === File,
    );

    const queryBuilder = this.fileRepository.createQueryBuilder('file');
    const whereConditions: string[] = [];

    fileRelations.forEach((relation) => {
      const entityTableName = relation.entityMetadata.tableName;
      // 左连接关联表
      queryBuilder.leftJoin(
        entityTableName,
        entityTableName,
        `${entityTableName}.${relation.propertyName} = file.id`,
      );
      // 添加无匹配记录的筛选条件
      whereConditions.push(`${entityTableName}.id IS NULL`);
    });

    if (whereConditions.length > 0) {
      queryBuilder.where(whereConditions.join(' AND '));
    } else {
      // 无任何关联表时，所有file都是未使用状态
      return this.fileRepository.find();
    }

    return queryBuilder.getMany();
  }
}

这个方案无需依赖数据库特定系统表，完全通过TypeORM元数据实现，跨数据库兼容性更好。

方案3：给File实体添加反向关联（推荐） #

如果可以修改实体结构，给File实体添加所有业务表的反向@OneToMany关联，就能直接通过查询构建器筛选无引用的File：

首先修改File实体：

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
import { Contact } from '../contact/entities/contact.entity';
import { Manager } from '../manager/entities/manager.entity';

@Entity()
export class File {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  s3Key: string;

  // 反向关联Contact的image字段
  @OneToMany(() => Contact, contact => contact.image)
  contacts: Contact[];

  // 反向关联Manager的image字段
  @OneToMany(() => Manager, manager => manager.image)
  managers: Manager[];
}

然后在清理服务中直接查询：

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { File } from './entities/file.entity';

@Injectable()
export class FileCleanupService {
  constructor(
    @InjectRepository(File)
    private fileRepository: Repository<File>,
  ) {}

  async findUnusedFiles(): Promise<File[]> {
    return this.fileRepository
      .createQueryBuilder('file')
      .leftJoin('file.contacts', 'contacts')
      .leftJoin('file.managers', 'managers')
      .where('contacts.id IS NULL')
      .andWhere('managers.id IS NULL')
      .getMany();
  }
}

这个方案代码直观易读，但需要维护File实体的反向关联，后续新增业务表时需同步添加对应关联。

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