Node.js读取Excel按行依赖插入MySQL多表报外键约束错误排查

业务场景要求读取Excel文件内容：Excel内有一列专门标识当前行是否为主行，其余非主行都是对应主行的从属子行。逻辑要求先把主行数据插入主表，获取主表插入返回的last_insertId之后，再把对应子行数据存入关联子表，但执行过程中持续触发子表外键相关错误，当前实现方案如下。

读取Excel内容代码 #

let reader = require('xlsx');

module.exports.storeData = () =>{

const file = reader.readFile('./data/file.xlsx');
  
let data = []
  
const sheets = file.SheetNames
  
// for(let i = 0; i < sheets.length; i++)
// {
   const temp = reader.utils.sheet_to_json(
    file.Sheets[file.SheetNames[sheets.indexOf('sheet-name')]])
   temp.forEach((res) => {
      data.push(res)
   })
// }
this.pushToDB(data);

}

行类型判断、分表插入逻辑代码 #

已经使用async异步处理，但无法正确异步获取last_insertId，出现部分主表数据插入成功但子表数据插入失败的问题，代码如下：

let last_insert_id = 0;

const executeQuery = async (con, query, params) => {
    return new Promise((resolve, reject) => {
       con.query(query, params, (err, result) => {
           if (err) { return reject(err); }
 
           return resolve(result);
       });
    });
 }


 module.exports.pushToDB = async (data) => {
    var mysql = require('mysql');  
    var con = mysql.createConnection({  
    host: "localhost",  
    user: "root",  
    password: "",  
    database: "data-db"  
    });  
    
     con.connect(async function(err) {  
        if (err) throw err;  
        console.log("Connected!");

        data.forEach(async (res, ind) => {

            try {
                
                if(res['Primary_Col'] == 'Yes'){
                    
                    let param = ['col1']
                    const query1 = "INSERT INTO Main_Table (COL1) VALUES (?)";
                    const result = await executeQuery(con, query1, param);

                    last_insert_id = result.insertId;

                    console.log(last_insert_id);
                    
                }
                
                if(res['Primary_Col'] == 'No'){
                    let param = ['col1', 'col2', 'col3' ]
                    const query1 = "INSERT INTO SUB_TABLE (COL1, COL2, COL3) VALUES (?,?,?)";
                    const result = await executeQuery(con, query1, param.map(String)); //param array values will be changes to string
                }

            } catch (err) {
                con.destroy;
                throw err;
            }
        });
    });
}

this.storeData();

报错信息 #

执行时抛出的具体错误如下：

Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails

报错是三个代码逻辑问题共同导致的：

• 用forEach遍历执行异步操作：forEach本身不支持等待异步任务完成，遍历会一次性触发所有行的处理逻辑，不会等上一行主表插入完成拿到insertId再处理后续行，子行插入时要么拿不到正确的主行ID，要么主行还没插入完成就先触发了子行写入，自然触发外键约束错误。
• 子表插入的SQL和参数里根本没传主表关联ID：就算拿到了正确的last_insert_id，现有子表插入语句没有写入外键字段，外键列要么是null要么是默认值，匹配不到主表记录肯定报错。
• 全局定义last_insert_id变量本身就有并发安全问题，多主行场景下值会互相覆盖，子行根本拿不到自己所属主行的正确ID。

1. 替换forEach为for...of循环，保证每一行的处理按顺序执行，上一个异步操作完成后再处理下一行
2. 子表插入时把当前所属主行的ID作为外键参数传入SQL，补全SQL里的外键字段占位符
3. 移除全局的last_insert_id变量，在循环作用域内维护当前主行ID，避免值混乱
4. 把数据库连接逻辑包装为Promise，保证连接成功后再执行数据操作，修正con.destroy未执行的语法问题

修复后的插入逻辑核心代码：

module.exports.pushToDB = async (data) => {
    const mysql = require('mysql');  
    const con = mysql.createConnection({  
        host: "localhost",  
        user: "root",  
        password: "",  
        database: "data-db"  
    });
    // 包装连接逻辑为Promise，等待连接成功再往下执行
    await new Promise((resolve, reject) => {
        con.connect((err) => {
            if(err) return reject(err);
            console.log("Connected!");
            resolve();
        })
    })

    // 用for...of替代forEach，保证顺序执行
    let currentMainId = 0;
    for (const res of data) {
        try {
            if(res['Primary_Col'] == 'Yes'){
                const param = ['col1']
                const insertMainSql = "INSERT INTO Main_Table (COL1) VALUES (?)";
                const result = await executeQuery(con, insertMainSql, param);
                currentMainId = result.insertId;
                console.log('主表插入成功，ID：', currentMainId);
            }
            
            if(res['Primary_Col'] == 'No'){
                // 把主表ID作为外键参数传入，注意替换外键字段名为实际业务字段名
                let param = ['col1', 'col2', 'col3', currentMainId]
                // 注意修改SQL，补充外键字段占位符，替换MAIN_ID为实际的子表外键列名
                const insertSubSql = "INSERT INTO SUB_TABLE (COL1, COL2, COL3, MAIN_ID) VALUES (?,?,?,?)";
                await executeQuery(con, insertSubSql, param.map(String));
            }
        } catch (err) {
            con.destroy();
            throw err;
        }
    }
    // 所有操作完成后关闭连接
    con.end();
}

注意：需要把上述代码里的子表外键字段名MAIN_ID替换为业务里实际的子表关联主表的列名。

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