如何在Express.js+React.js中同时保存主表与关联表元素

问题背景 #

项目基于Express.js和React.js，使用MySQL数据库，包含Customers（对应需求中的Contacts）、Workers主表及关联表workercustomers（对应WorkerContacts）。需求是创建联系人时同步创建对应的关联记录，且需保证事务一致性——任一环节失败则全部回滚。当前核心问题：插入关联表时无法获取新创建联系人的自增ID，导致关联记录插入失败。

当前代码问题 #

控制器创建函数 #

export const create = (req, res) => {
    const customer = new Customer(
        null, req.body.customer.company, req.body.customer.contact_name, req.body.customer.email, req.body.customer.number, req.body.customer.title, req.body.customer.old_address, req.body.customer.new_address, req.body.customer.category,  
        req.body.customer.broker_name, req.body.customer.broker_company, req.body.customer.broker_number, req.body.customer.broker_email, req.body.customer.architect_name, req.body.customer.architect_company, req.body.customer.architect_number, req.body.customer.architect_email,  
        req.body.customer.consultant_name, req.body.customer.consultant_company, req.body.customer.consultant_number, req.body.customer.consultant_email, ""
        )
    Customer.companyValidator(customer.company)
    .then(([found_customer_element]) => {
        if (found_customer_element.length !== 0){
            res.status(406).json({message: "Company already has an associated customer"});
        }
        
        else{
            (customer.customerValidator() && req.body.workers.length !== 0) ? 
            customer.save(req.body.workers)
            .then((result) => {
                Customer.findByID(result[0].insertId)
                .then(([new_customer]) =>{
                    res.json({customer: new_customer, workers: req.body.workers})
                })
                .catch(err => res.status(500).json({message: "We had some trouble saving on our end. Please try to reload page and try again"}))
                
            })
            .catch(err => res.status(500).json({message: "We had some trouble saving on our end. Please try to reload page and try again"}))
            :
            res.status(406).json({message: "Must have company name, customer name, workers, and category filled"});
        }
    })
    .catch(err => res.status(500).json({message: "Something went wrong on our end, please try to reload page and try again"}) )
}

数据库保存函数 #

async save(worker_list){
        // The purpose of this function is to save a new element to the database.
        try{
            await db.execute(`INSERT INTO customers (company, contact_name, contact_email, contact_phone_number, contact_title, old_address, new_address, category, broker_name, broker_company,
                broker_number, broker_email, architect_name, architect_company, architect_number, architect_email, consultant_name, consultant_company, consultant_number, consultant_email, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, 
                [this.company, this.contact_name, this.contact_email, this.contact_phone_number, this.contact_title, this.old_address, this.new_address, this.category, this.broker_name, this.broker_company, this.broker_number, this.broker_email,
                this.architect_name, this.architect_company, this.architect_number, this.architect_email, this.consultant_name, this.consultant_company, this.consultant_number, this.consultant_email, this.notes]);
            for (const worker of worker_list){
                console.log(this.id) // this.id为null，无法使用
                await db.execute(`INSERT INTO workercustomers (customer_id, worker_id) VALUES(?, ?)`, [this.id, worker.value])
            }
            await db.execute("COMMIT");
        }
        catch (err){
            await db.execute("ROLLBACK")
        }
    }

解决方案 #

核心思路：从INSERT customers的执行结果中获取自增ID，同时确保事务完整开启与闭合。

修改后的数据库保存函数 #

async save(worker_list){
    try{
        // 1. 显式开启事务
        await db.execute("START TRANSACTION");
        
        // 2. 插入客户记录并获取自增ID
        const [insertResult] = await db.execute(
            `INSERT INTO customers (company, contact_name, contact_email, contact_phone_number, contact_title, old_address, new_address, category, broker_name, broker_company,
            broker_number, broker_email, architect_name, architect_company, architect_number, architect_email, consultant_name, consultant_company, consultant_number, consultant_email, notes) 
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, 
            [this.company, this.contact_name, this.contact_email, this.contact_phone_number, this.contact_title, this.old_address, this.new_address, this.category, this.broker_name, this.broker_company, this.broker_number, this.broker_email,
            this.architect_name, this.architect_company, this.architect_number, this.architect_email, this.consultant_name, this.consultant_company, this.consultant_number, this.consultant_email, this.notes]
        );
        const customerId = insertResult.insertId; // 获取新插入客户的ID
        
        // 3. 使用获取到的ID批量插入关联记录
        for (const worker of worker_list){
            await db.execute(`INSERT INTO workercustomers (customer_id, worker_id) VALUES(?, ?)`, [customerId, worker.value])
        }
        
        // 4. 提交事务
        await db.execute("COMMIT");
        
        // 返回插入结果，供控制器使用
        return insertResult;
    }
    catch (err){
        // 事务回滚
        await db.execute("ROLLBACK");
        // 抛出错误，让控制器捕获处理
        throw err;
    }
}

关键调整说明 #

• 显式开启事务：在执行插入前调用START TRANSACTION，确保所有操作在同一事务内。
• 获取自增ID：MySQL的execute方法返回的结果对象中包含insertId属性，对应自增主键的值，直接提取即可。
• 错误抛出：在catch块中抛出错误，让控制器的catch分支能捕获并返回对应错误信息，避免静默失败。
• 返回结果：将插入客户的结果返回给控制器，保持原有的查询新客户信息的逻辑正常运行。

这样修改后，既能保证事务一致性，又能正确获取新联系人ID完成关联表插入，解决数据不一致和ID缺失的问题。

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