在Node.js函数中执行两个SQL查询失败，寻求技术帮助

我有两个数据表：prodstock和stockmaster，想要执行两个SQL操作：往stockmaster插入库存记录，同时更新prodstock的总数量，但目前功能无法正常工作，详情如下：

表结构 #

• stockmaster表：包含字段stocknum、cat_id、user_id、dyenumber、stockQty、price、stockform、remark
• prodstock表：包含字段cat_id、dyenumber、total_qty（需更新的总数量字段）

当前代码 #

生成SQL语句的函数 #

export const postStock = (body) => {
    let sql = ` INSERT INTO stockmaster (stocknum, cat_id, user_id, dyenumber, stockQty, price,stockform, remark) VALUES ('${body.stocknum}', '${body.cat_id}', '${body.user_id}', '${body.dyenumber}', '${body.stockQty}', '${body.price}', '${body.stockform}', '${body.remark}')`;
    return sql;   
};

export const updateprodStock = (cat_id, dyenumber, stockQty) => {
    let sql = `UPDATE prodstock JOIN stockmaster ON prodstock.cat_id = '${cat_id}' AND prodstock.dyenumber = '${dyenumber}' SET prodstock.total_qty = prodstock.total_qty + '${stockQty} `
    return sql}

调用函数的代码 #

static stock = (req, res) => {
        const { cat_id, dyenumber, stockQty } = req.body;
        connection.query(postStock(req.body), (err, result) => {
            if (err) {
                throw new Error(err);
            } else {
                connection.query(updateprodStock(cat_id, dyenumber, stockQty))
                res.status(200).json({
                    code: 1,
                    msg: "success",
                    data: result
                })
            }
        })
    }

问题排查与修复方案 #

1. 修复SQL语法错误 #

updateprodStock函数的SQL语句存在两处语法错误，还有冗余逻辑：

• 结尾缺少闭合单引号和语句分号
• 无需关联stockmaster：更新prodstock仅需通过cat_id和dyenumber定位目标记录，多余的JOIN可能导致逻辑异常

修正后的updateprodStock函数：

export const updateprodStock = (cat_id, dyenumber, stockQty) => {
    let sql = `UPDATE prodstock SET prodstock.total_qty = prodstock.total_qty + ${stockQty} WHERE prodstock.cat_id = '${cat_id}' AND prodstock.dyenumber = '${dyenumber}';`
    return sql;
}

2. 修正异步操作顺序 #

当前代码在第一个查询执行后直接返回响应，第二个查询可能还未完成，导致更新不可靠。需在第二个查询的回调中统一返回响应，并处理更新错误：

修正后的调用代码：

static stock = (req, res) => {
    const { cat_id, dyenumber, stockQty } = req.body;
    connection.query(postStock(req.body), (err, insertResult) => {
        if (err) {
            return res.status(500).json({ code: 0, msg: "插入失败", error: err.message });
        }
        // 等待更新操作完成后再响应
        connection.query(updateprodStock(cat_id, dyenumber, stockQty), (updateErr, updateResult) => {
            if (updateErr) {
                return res.status(500).json({ code: 0, msg: "更新失败", error: updateErr.message });
            }
            res.status(200).json({
                code: 1,
                msg: "插入和更新都成功",
                insertData: insertResult,
                updateData: updateResult
            });
        });
    });
}

3. 解决SQL注入风险 #

直接拼接用户输入到SQL语句中存在极高注入风险，必须改用参数化查询（以mysql2为例）：

// 插入函数（参数化版本）
export const postStock = (body) => {
    const sql = `INSERT INTO stockmaster (stocknum, cat_id, user_id, dyenumber, stockQty, price, stockform, remark) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;
    const values = [body.stocknum, body.cat_id, body.user_id, body.dyenumber, body.stockQty, body.price, body.stockform, body.remark];
    return { sql, values };
};

// 更新函数（参数化版本）
export const updateprodStock = (cat_id, dyenumber, stockQty) => {
    const sql = `UPDATE prodstock SET total_qty = total_qty + ? WHERE cat_id = ? AND dyenumber = ?;`;
    const values = [stockQty, cat_id, dyenumber];
    return { sql, values };
};

// 调用代码
static stock = (req, res) => {
    const { cat_id, dyenumber, stockQty } = req.body;
    const insertQuery = postStock(req.body);
    connection.query(insertQuery.sql, insertQuery.values, (err, insertResult) => {
        if (err) {
            return res.status(500).json({ code: 0, msg: "插入失败", error: err.message });
        }
        const updateQuery = updateprodStock(cat_id, dyenumber, stockQty);
        connection.query(updateQuery.sql, updateQuery.values, (updateErr, updateResult) => {
            if (updateErr) {
                return res.status(500).json({ code: 0, msg: "更新失败", error: updateErr.message });
            }
            res.status(200).json({
                code: 1,
                msg: "操作成功",
                insertData: insertResult,
                updateData: updateResult
            });
        });
    });
}

4. 事务保障（可选但推荐） #

如果要求两个操作必须同时成功或失败（比如插入成功但更新失败时回滚插入），需使用数据库事务：

static stock = (req, res) => {
    const { cat_id, dyenumber, stockQty } = req.body;
    // 开启事务
    connection.beginTransaction((err) => {
        if (err) {
            return res.status(500).json({ code: 0, msg: "事务开启失败", error: err.message });
        }
        // 执行插入
        const insertQuery = postStock(req.body);
        connection.query(insertQuery.sql, insertQuery.values, (insertErr, insertResult) => {
            if (insertErr) {
                return connection.rollback(() => {
                    res.status(500).json({ code: 0, msg: "插入失败，事务回滚", error: insertErr.message });
                });
            }
            // 执行更新
            const updateQuery = updateprodStock(cat_id, dyenumber, stockQty);
            connection.query(updateQuery.sql, updateQuery.values, (updateErr, updateResult) => {
                if (updateErr) {
                    return connection.rollback(() => {
                        res.status(500).json({ code: 0, msg: "更新失败，事务回滚", error: updateErr.message });
                    });
                }
                // 提交事务
                connection.commit((commitErr) => {
                    if (commitErr) {
                        return connection.rollback(() => {
                            res.status(500).json({ code: 0, msg: "事务提交失败", error: commitErr.message });
                        });
                    }
                    res.status(200).json({
                        code: 1,
                        msg: "插入和更新成功，事务提交",
                        insertData: insertResult,
                        updateData: updateResult
                    });
                });
            });
        });
    });
}

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