如何在Sequelize中实现按用户维度递增的发票序列ID？

首先明确：绝对不需要为每个用户单独配置数据库，这属于过度设计，会导致数据库管理、连接池维护、备份同步等一系列不必要的麻烦，完全没必要。

下面是两种可靠的实现方案：

方案一：新增用户发票计数器表（推荐） #

创建一张专门记录每个用户发票编号序列的表，通过事务保证编号生成的原子性，避免并发冲突。

1. 定义计数器模型 #

module.exports = (sequelize, Sequelize) => {
  const UserInvoiceCounter = sequelize.define("user_invoice_counter", {
    userId: {
      type: Sequelize.BIGINT,
      primaryKey: true,
      allowNull: false,
      references: {
        model: 'user',
        key: 'id',
      },
    },
    currentNumber: {
      type: Sequelize.INTEGER,
      allowNull: false,
      defaultValue: 0,
    }
  });

  return UserInvoiceCounter;
};

2. 创建发票时的事务逻辑 #

每次生成发票前，先通过事务更新计数器并获取最新编号，再插入发票记录：

async function createInvoice(userId, invoiceData) {
  const transaction = await sequelize.transaction();
  try {
    // 先获取并递增用户的发票计数器
    const counter = await UserInvoiceCounter.findOne({
      where: { userId },
      transaction,
      lock: transaction.LOCK.UPDATE, // 加锁防止并发冲突
    });

    let userInvoiceNumber;
    if (counter) {
      userInvoiceNumber = counter.currentNumber + 1;
      await counter.update({ currentNumber: userInvoiceNumber }, { transaction });
    } else {
      // 用户首次创建发票，初始化计数器
      userInvoiceNumber = 1;
      await UserInvoiceCounter.create({
        userId,
        currentNumber: userInvoiceNumber,
      }, { transaction });
    }

    // 格式化编号为4位（比如0001、0002）
    const formattedInvoiceNumber = userInvoiceNumber.toString().padStart(4, '0');

    // 创建发票记录
    const invoice = await Invoice.create({
      ...invoiceData,
      userId,
      userInvoiceNumber, // 存储原始数字，方便后续计算
      formattedInvoiceNumber, // 也可以直接存格式化后的字符串
    }, { transaction });

    await transaction.commit();
    return invoice;
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

方案二：直接通过查询用户已有发票数量生成（需注意并发） #

如果不想新增表，可以在创建发票时，先查询该用户已有的发票数量，加1作为新编号，但必须用事务+行锁保证原子性：

async function createInvoice(userId, invoiceData) {
  const transaction = await sequelize.transaction();
  try {
    // 查询用户已有发票数量，加锁防止其他事务同时插入
    const invoiceCount = await Invoice.count({
      where: { userId },
      transaction,
      lock: transaction.LOCK.UPDATE,
    });

    const userInvoiceNumber = invoiceCount + 1;
    const formattedInvoiceNumber = userInvoiceNumber.toString().padStart(4, '0');

    const invoice = await Invoice.create({
      ...invoiceData,
      userId,
      userInvoiceNumber,
      formattedInvoiceNumber,
    }, { transaction });

    await transaction.commit();
    return invoice;
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

这种方案的缺点是：如果用户有大量发票，count查询的性能会比计数器表差一些；并且如果存在删除发票的场景，编号会出现断层（比如删除了0002，下一张会是0003而不是补0002），如果业务允许编号断层，这种方案可以用。

发票表结构调整 #

原有的id字段可以保留作为全局唯一主键（数据库自增即可），新增userInvoiceNumber（用户维度的递增数字）和formattedInvoiceNumber（格式化后的显示编号，比如0001）两个字段，这样既保证了全局唯一的主键，又满足了用户维度的编号需求。

调整后的发票模型示例：

module.exports = (sequelize, Sequelize) => {
  const Invoice = sequelize.define("invoice", {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      allowNull: false,
      autoIncrement: true, // 保留全局自增主键
    },
    userId: {
      type: Sequelize.BIGINT,
      allowNull: false,
      references: {
        model: 'user',
        key: 'id',
      },
    },
    userInvoiceNumber: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },
    formattedInvoiceNumber: {
      type: Sequelize.STRING(10),
      allowNull: false,
    },
    contactId: {
      type: Sequelize.BIGINT,
      allowNull: false,
      references: {
        model: 'contact',
        key: 'id',
      },
    },
    dueDays: {
      type: Sequelize.BIGINT,
      allowNull: false,
    },
    subTotal: {
      type: Sequelize.DECIMAL,
      allowNull: true,
    },
    // 其他字段...
  });

  // 添加联合唯一约束，确保每个用户的发票编号唯一
  Invoice.addConstraint('unique_user_invoice_number', {
    fields: ['userId', 'userInvoiceNumber'],
    type: 'unique',
  });

  return Invoice;
};

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