NestJS+TypeORM+MySQL如何单查询跨两仓库搜索并统一分页排序

这个需求完全可落地，性能最优的实现方式是在数据库层通过UNION ALL合并两个表的结果集，直接在SQL层面完成统一排序、分页，不需要把全量数据拉到应用内存处理，数据量较大时也能保持稳定性能。

MySQL的UNION语法要求参与合并的两个子查询返回的列数量、列顺序、对应位置列的数据类型完全兼容，所以你需要给两个子查询补全对方独有的字段，用NULL占位，同时加一个固定值的类型标记字段，方便后续区分结果是店铺还是优惠活动。

核心实现代码如下：

async unifiedSearch(filter: { description?: string; skip: number; take: number }) {
  // 构造店铺查询子句，补全优惠独有字段为NULL做占位
  const shopSubQuery = this.shopRepository
    .createQueryBuilder('shop')
    .select([
      'shop.id AS id',
      "'shop' AS result_type",
      'shop.name AS name',
      'shop.description AS description',
      'shop.image_url AS image_url',
      'shop.is_active AS is_active',
      'shop.is_special AS is_special',
      'NULL AS fabric',
      'NULL AS code',
      'NULL AS start_date',
      'NULL AS end_date',
      'shop.created_at AS created_at'
    ])
    .where('shop.description LIKE :shopDesc', {
      shopDesc: filter.description ? `%${filter.description}%` : '%'
    })

  // 构造优惠查询子句，补全店铺独有字段为NULL做占位
  const offerSubQuery = this.offerRepository
    .createQueryBuilder('offer')
    .select([
      'offer.id AS id',
      "'offer' AS result_type",
      'offer.name AS name',
      'offer.description AS description',
      'NULL AS image_url',
      'NULL AS is_active',
      'NULL AS is_special',
      'offer.fabric AS fabric',
      'offer.code AS code',
      'offer.start_date AS start_date',
      'offer.end_date AS end_date',
      'offer.created_at AS created_at'
    ])
    .where('offer.description LIKE :offerDesc', {
      offerDesc: filter.description ? `%${filter.description}%` : '%'
    })

  // 合并两个子查询，执行统一排序分页
  const rawList = await this.shopRepository
    .createQueryBuilder()
    .select('*')
    .from(`(${shopSubQuery.getQuery()} UNION ALL ${offerSubQuery.getQuery()})`, 'unified_res')
    .setParameters({
      ...shopSubQuery.getParameters(),
      ...offerSubQuery.getParameters()
    })
    .orderBy('unified_res.created_at', 'DESC')
    .skip(filter.skip)
    .take(filter.take)
    .getRawMany()

  // 可选：按类型格式化结果，方便前端区分处理
  return rawList.map(item => {
    if (item.result_type === 'shop') {
      return {
        type: 'shop',
        data: {
          id: item.id,
          name: item.name,
          description: item.description,
          image_url: item.image_url,
          is_active: !!item.is_active,
          is_special: !!item.is_special,
          created_at: item.created_at
        }
      }
    }
    return {
      type: 'offer',
      data: {
        id: item.id,
        name: item.name,
        description: item.description,
        fabric: item.fabric,
        code: item.code,
        start_date: item.start_date,
        end_date: item.end_date,
        created_at: item.created_at
      }
    }
  })
}

注意事项 #

• 优先用UNION ALL而非UNION：UNION会对全量合并结果做去重扫描，性能损耗大，两个表的主键都是UUID，不存在跨表ID重复的问题，UNION ALL无去重逻辑，执行效率高很多。
• 字段顺序必须严格对齐：两个子查询SELECT的字段顺序要完全对应，同位置字段的数据类型要兼容，避免MySQL隐式类型转换导致结果异常。
• 参数要统一绑定：两个子查询的预编译参数需要合并后传给外层查询，避免参数丢失报错。
• 分页总数计算：如果需要返回总记录数给前端渲染分页组件，单独写一个统计查询即可，把外层查询的SELECT子句换成COUNT(1) as total，去掉排序、skip、take逻辑就能拿到准确总数。
• 扩展过滤条件：后续如果要加筛选规则，比如只展示启用状态的店铺、只展示生效中的优惠，直接在对应的子查询里追加WHERE条件即可，不需要改动外层合并逻辑。

如果你的业务数据量非常小（两个表总数据量不超过千条），也可以用更简单的内存合并方案：分别执行两个表的过滤查询（查询时不要加skip、take限制），拿到两个结果数组后合并为单个数组，按created_at字段倒序排序，再手动用slice(filter.skip, filter.skip + filter.take)截取分页数据。这个方案写起来更简单，但数据量上去之后查询性能、内存占用都会明显变差，不推荐面向C端的接口使用。

内容的提问来源于stack exchange，提问作者Nour Maher Taha