如何用Node.js将Excel解析的JSON数据存入MySQL关联表？

已实现的代码与现状 #

1. app.js中Excel解析接口（POST /data） #

app.post('/data', (req, res) =>{
    const file = req.files.filename;
    const nose = [];
    const filename = file.name;
    file.mv('./excel/'+filename, (err)=>{
        if(err){
            console.log(err)
        } else{
            const result = importExcel({
                sourceFile: './excel/'+filename,
                header: {rows:1},
                columnToKey :{A:'Dimension', B:'Category', C:'Subcategory',D:'Factor', E:'Context', F:'Date',G:'Indicator', H:'Formula',I:'FoundValue'},
                sheets:['data']

            })
            for(var i=0; result.data.length > i; i++){
                nose.push(result.data[i].Dimension,
                            result.data[i].Category)
            }
            res.send(nose)
            console.log(nose+' = Total data '+nose.length);
        }
    })
})

2. 前端上传表单（data.ejs） #

<form action="/data" method="post" enctype="multipart/form-data">
    <div style="float:right">
        <input style="margin-top:6%;" class="btn btn-light" type="file" name="filename"> 
        <button style="float:right" type="submit" class="btn btn-success mt-4"><i class="fa-solid fa-file-import"></i></button>
    </div>
    <div class="footer" style="margin-left: 0px; margin-right: 0px!important;">

3. 数据展示接口（GET /data） #

app.get('/data', (req, res) =>{
    connection.query('SELECT c.data_id, d.dimension_name, cc.category_name, s.subcategory_name, f.factor_name,f.factor_description,c.date_creation_data, c.Indicator,ff.formula_name FROM data_load c INNER JOIN dimensions d ON c.dimension_id = d. id_dimensions INNER JOIN categories cc ON c.id_categories = cc.id_categories INNER JOIN subcategory s ON c.id_subcategories = s.id_subcategories INNER JOIN factors f ON c.id_factor = f.id_factor INNER JOIN formulas ff ON c.id_formula = ff.id_formula;',
    function(error, results){
        if(error){
            console.log(error)
        } else{
            res.render('./data', {
                results:results
            })
        }
    })
})

现状说明 #

• 当前页面显示：右侧悬浮的文件选择按钮与导入按钮
• 解析后的数据显示：维度和类别的数组列表，与Excel内容完全匹配
• Excel内容：包含Dimension、Category、Subcategory、Factor等列的表格数据

数据库关联表结构 #

核心关联表carga_datos的创建语句：

CREATE TABLE `carga_datos` (
  `id_datos` int(11) NOT NULL,
  `id_dimensiones` int(11) DEFAULT NULL,
  `id_categorias` int(11) DEFAULT NULL,
  `id_subcategorias` int(11) DEFAULT NULL,
  `id_factor` int(11) DEFAULT NULL,
  `fecha_creacion_data` timestamp NOT NULL DEFAULT current_timestamp(),
  `Indicador` int(11) NOT NULL,
  `id_formula` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `carga_datos`
  ADD PRIMARY KEY (`id_datos`),
  ADD KEY `id_dimensiones` (`id_dimensiones`),
  ADD KEY `id_categorias` (`id_categorias`),
  ADD KEY `id_subcategorias` (`id_subcategorias`),
  ADD KEY `id_factor` (`id_factor`),
  ADD KEY `id_formula` (`id_formula`);

ALTER TABLE `carga_datos`
  MODIFY `id_datos` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `carga_datos`
  ADD CONSTRAINT `carga_datos_ibfk_1` FOREIGN KEY (`id_dimensiones`) REFERENCES `dimensiones` (`id_dimensiones`),
  ADD CONSTRAINT `carga_datos_ibfk_2` FOREIGN KEY (`id_categorias`) REFERENCES `categorias` (`id_categorias`),
  ADD CONSTRAINT `carga_datos_ibfk_3` FOREIGN KEY (`id_subcategorias`) REFERENCES `subcategoria` (`id_subcategorias`),
  ADD CONSTRAINT `carga_datos_ibfk_4` FOREIGN KEY (`id_factor`) REFERENCES `factores` (`id_factor`),
  ADD CONSTRAINT `carga_datos_ibfk_5` FOREIGN KEY (`id_formula`) REFERENCES `formulas` (`id_formula`);
COMMIT;

需求 #

将解析得到的Excel数据存入上述带外键关联的MySQL表，需要新手易理解的实现方案，已知multer、sequelize等工具，但希望用简单的方式实现。

解决方案（基于原生mysql模块，新手友好） #

核心思路 #

carga_datos存储的是关联表的ID而非名称，因此需要：

1. 对每条解析后的Excel数据，先查询对应关联表（如dimensiones、categorias）中名称对应的ID
2. 拿到所有关联ID后，将数据插入carga_datos

用async/await处理异步操作，避免回调地狱，代码逻辑更线性清晰。

修改后的app.js POST接口代码 #

// 改为async函数处理异步流程
app.post('/data', async (req, res) =>{
    try {
        const file = req.files.filename;
        const filename = file.name;
        // 移动文件到指定目录（用Promise包装回调）
        await new Promise((resolve, reject) => {
            file.mv('./excel/'+filename, (err)=>{
                if(err) reject(err);
                else resolve();
            })
        });

        // 解析Excel
        const result = importExcel({
            sourceFile: './excel/'+filename,
            header: {rows:1},
            columnToKey :{A:'Dimension', B:'Category', C:'Subcategory',D:'Factor', E:'Context', F:'Date',G:'Indicator', H:'Formula',I:'FoundValue'},
            sheets:['data']
        });

        // 循环处理每条Excel数据
        for(const item of result.data) {
            // 1. 查询维度ID
            const [dimensionRows] = await new Promise((resolve, reject) => {
                connection.query('SELECT id_dimensiones FROM dimensiones WHERE dimension_name = ?', [item.Dimension], (err, rows) => {
                    if(err) reject(err);
                    else resolve(rows);
                })
            });
            const id_dimensiones = dimensionRows?.[0]?.id_dimensiones;

            // 2. 查询类别ID
            const [categoryRows] = await new Promise((resolve, reject) => {
                connection.query('SELECT id_categorias FROM categorias WHERE category_name = ?', [item.Category], (err, rows) => {
                    if(err) reject(err);
                    else resolve(rows);
                })
            });
            const id_categorias = categoryRows?.[0]?.id_categorias;

            // 3. 查询子类别ID
            const [subcategoryRows] = await new Promise((resolve, reject) => {
                connection.query('SELECT id_subcategorias FROM subcategory WHERE subcategory_name = ?', [item.Subcategory], (err, rows) => {
                    if(err) reject(err);
                    else resolve(rows);
                })
            });
            const id_subcategorias = subcategoryRows?.[0]?.id_subcategorias;

            // 4. 查询因子ID
            const [factorRows] = await new Promise((resolve, reject) => {
                connection.query('SELECT id_factor FROM factores WHERE factor_name = ?', [item.Factor], (err, rows) => {
                    if(err) reject(err);
                    else resolve(rows);
                })
            });
            const id_factor = factorRows?.[0]?.id_factor;

            // 5. 查询公式ID
            const [formulaRows] = await new Promise((resolve, reject) => {
                connection.query('SELECT id_formula FROM formulas WHERE formula_name = ?', [item.Formula], (err, rows) => {
                    if(err) reject(err);
                    else resolve(rows);
                })
            });
            const id_formula = formulaRows?.[0]?.id_formula;

            // 6. 插入到carga_datos表（确保所有关联ID都存在）
            if(id_dimensiones && id_categorias && id_subcategorias && id_factor && id_formula) {
                await new Promise((resolve, reject) => {
                    connection.query(
                        'INSERT INTO carga_datos (id_dimensiones, id_categorias, id_subcategorias, id_factor, Indicador, id_formula) VALUES (?, ?, ?, ?, ?, ?)',
                        [id_dimensiones, id_categorias, id_subcategorias, id_factor, item.Indicator, id_formula],
                        (err) => {
                            if(err) reject(err);
                            else resolve();
                        }
                    )
                });
                console.log(`成功插入数据：${item.Dimension} - ${item.Category}`);
            } else {
                console.log(`数据缺失关联ID，跳过：${JSON.stringify(item)}`);
            }
        }

        res.send('数据导入完成');
    } catch (err) {
        console.error(err);
        res.status(500).send('导入失败：' + err.message);
    }
})

补充说明 #

1. 关联数据不存在的处理：如果Excel中的维度/类别等在关联表中不存在，代码会跳过这条数据。若需要自动新增关联数据，可在查询无结果时执行插入语句，再获取新生成的ID。
2. 异步逻辑简化：用Promise包装原生的connection.query回调，配合async/await让代码流程更直观，新手更容易跟进。
3. 错误捕获：用try/catch包裹整个流程，统一处理文件移动、Excel解析、数据库操作中的错误，返回明确的提示信息。

内容的提问来源于stack exchange，提问作者Sofia Blanco Sanchez