NestJS+TypeORM实现MySQL带前缀自增主键与外键关联

一、实现带前缀的自增ID #

方案1：实体钩子 + 数据库自增字段 #

这种方式依赖数据库自增数字字段，在插入前通过实体钩子拼接前缀生成最终ID，简单易维护。

RevenueGenerator 实体

import { Entity, Column, BeforeInsert, PrimaryColumn } from 'typeorm';

@Entity()
export class RevenueGenerator {
  @PrimaryColumn()
  id: string; // 最终生成 RG101、RG102 格式的ID

  // 数据库自增的数字字段，仅用于生成前缀ID，对外隐藏
  @Column({ type: 'int', unsigned: true, generated: 'increment', unique: true })
  private autoId: number;

  @Column()
  name: string;

  @Column()
  audience: string;

  // 插入前自动生成带前缀的ID
  @BeforeInsert()
  generateId() {
    this.id = `RG${this.autoId.toString().padStart(3, '0')}`;
  }
}

RevenueStream 实体

import { Entity, Column, BeforeInsert, PrimaryColumn, ManyToOne, JoinColumn } from 'typeorm';
import { RevenueGenerator } from './revenue-generator.entity';

@Entity()
export class RevenueStream {
  @PrimaryColumn()
  id: string; // 最终生成 RS101、RS102 格式的ID

  @Column({ type: 'int', unsigned: true, generated: 'increment', unique: true })
  private autoId: number;

  @Column()
  tax: number;

  @Column()
  sharepercentage: number;

  @ManyToOne(() => RevenueGenerator)
  @JoinColumn({ name: 'revenueGeneratorId' })
  revenueGenerator: RevenueGenerator;

  @BeforeInsert()
  generateId() {
    this.id = `RS${this.autoId.toString().padStart(3, '0')}`;
  }
}

方案2：自定义ID生成器 #

如果不想额外增加自增字段，可通过TypeORM的自定义ID生成策略，直接从现有ID中提取数字部分自增。

自定义生成器类

import { EntityManager, IDatabaseDriver, Connection } from 'typeorm';
import { AbstractIdGenerator } from 'typeorm/id/AbstractIdGenerator';

export class PrefixIdGenerator extends AbstractIdGenerator {
  async generateId(
    entity: any,
    manager: EntityManager<any>,
    connection: Connection<IDatabaseDriver<any>>,
  ): Promise<string> {
    // 根据实体类型设置前缀
    const prefix = entity.constructor.name === 'RevenueGenerator' ? 'RG' : 'RS';
    // 查询当前最大ID的数字部分，加悲观锁防止并发重复
    const maxEntity = await manager
      .createQueryBuilder(entity.constructor, 'e')
      .select('MAX(CAST(SUBSTRING(e.id, 3) AS UNSIGNED))', 'maxId')
      .setLock('pessimistic_write')
      .getRawOne();
    // 从101开始自增，不足3位补0
    const nextId = (maxEntity.maxId || 100) + 1;
    return `${prefix}${nextId.toString().padStart(3, '0')}`;
  }
}

实体中配置生成器

// RevenueGenerator 实体
@Entity()
export class RevenueGenerator {
  @PrimaryColumn({
    type: 'varchar',
    generated: 'custom',
    generator: { type: PrefixIdGenerator },
  })
  id: string;

  @Column()
  name: string;

  @Column()
  audience: string;
}

// RevenueStream 实体
@Entity()
export class RevenueStream {
  @PrimaryColumn({
    type: 'varchar',
    generated: 'custom',
    generator: { type: PrefixIdGenerator },
  })
  id: string;

  @Column()
  tax: number;

  @Column()
  sharepercentage: number;

  @ManyToOne(() => RevenueGenerator)
  @JoinColumn({ name: 'revenueGeneratorId' })
  revenueGenerator: RevenueGenerator;
}

二、自动关联外键 #

避免数据库触发器的不可控性，直接在应用层（Service）处理关联逻辑，确保外键自动插入：

Service 层代码示例 #

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { RevenueGenerator } from './entities/revenue-generator.entity';
import { RevenueStream } from './entities/revenue-stream.entity';

@Injectable()
export class RevenueService {
  constructor(
    @InjectRepository(RevenueGenerator)
    private readonly rgRepo: Repository<RevenueGenerator>,
    @InjectRepository(RevenueStream)
    private readonly rsRepo: Repository<RevenueStream>,
  ) {}

  // 同时创建RevenueGenerator和关联的RevenueStream
  async createWithRelation(rgData: any, rsData: any) {
    // 1. 先创建并保存RevenueGenerator，获取自动生成的ID
    const rg = this.rgRepo.create(rgData);
    await this.rgRepo.save(rg);

    // 2. 关联外键，创建并保存RevenueStream
    const rs = this.rsRepo.create({
      ...rsData,
      revenueGenerator: rg, // 直接关联实体，TypeORM会自动写入外键字段
    });
    await this.rsRepo.save(rs);

    return { revenueGenerator: rg, revenueStream: rs };
  }
}

注意事项 #

• 若使用自定义ID生成器，必须加悲观锁（setLock('pessimistic_write')），防止高并发场景下生成重复ID。
• 实体关联需正确配置@ManyToOne和@JoinColumn，确保TypeORM能自动处理外键映射。
• 优先选择应用层处理关联逻辑，比数据库触发器更易调试和维护。

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