Node.js脚本无财务数据时无法插入MySQL问题求助

AI 小北

2026-8-20

问题排查：无财务数据时无法插入全0记录到MySQL

我是JS和Node.js初学者，编写的脚本从MySQL查询公司列表，针对每家公司的2018-2021年调用第三方API获取财务数据；当存在财务数据时可正常插入Date_Financiare表，但无财务数据时无法插入全0值的记录，其余功能均正常。

相关代码

var mysql = require('mysql');
fetch = require('node-fetch');

var connection = mysql.createConnection({
  host: "host.ip",
  user: "user_name",
  password: "pass_word",
  database: "db_db"
});
var years_to_scrape = [2021, 2020, 2019, 2018];

connection.query("SELECT t1.CUI,t1.Denumire_Companie,Reg_Com,Status,Localitate FROM Date_Companii as t1 LEFT JOIN Date_Financiare as t2 ON t2.cui = t1.cui WHERE t2.AN IS NULL ORDER by t1.CUI DESC LIMIT 3", function(error, results, fields) {
  if (error) { return console.error(error.message);}

  var db_results = results;

  for (i in db_results) {
    var one_after_another = JSON.parse(JSON.stringify(db_results[i]));
    console.log(one_after_another.CUI);

    for (i in years_to_scrape){
      var url = 'https://webservicesp.anaf.ro/bilant?an=' + years_to_scrape[i]+ '&cui=' + one_after_another.CUI;
      
      fetch(url)
        .then(res => res.json())
        .then (json => {
          console.log(json)
          var r = json;
          console.log(r)

          var indicatori = JSON.parse(JSON.stringify(r.i))
          console.log (indicatori)

          if (indicatori.length==0){
            connection.query('INSERT INTO Date_Financiare(CUI,AN,Numar_Salariati,Pierdere_Neta,Profit_Net,Pierdere_Bruta,Profit_Brut,Cheltuieli_Totale,Venituri_Totale,Cifra_De_Afacere_Neta,Patrimoniul_Regiei,Capital_Subscris_Varsat,Capitaluri_Total,Provizioane,Venituri_In_Avans,Datorii,Cheltuieli_In_Avans,Casa_Si_Conturi,Creante,Stocuri,Active_Circulante,Active_Mobilizate,CAEN) VALUES ('+cuiul+","+anul+',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);', function(error,results,fields) {
              if (error) { /*return console.error(error.message)*/;}
              if(results){/*return console.log("results");*/}
              if(fields){/*return console.log("fields");*/}
              console.log("Inserted into DB");
            })
          } else {
            var anul = r.an;
            console.log("Anul: "+ anul)
            var cuiul = r.cui;
            console.log("CUI: "+ cuiul)
            var caen = r.caen;
            console.log("CAEN: "+caen)

            for (z in indicatori){
              if(indicatori[z].indicator === "I1"){
                var valoare_active_mobilizate = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I2"){
                var valoare_active_circulante = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I3"){
                var valoare_stocuri = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I4"){
                var valoare_creante = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I5"){
                var valoare_casa_si_conturi = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I6"){
                var valoare_cheltuieli_in_avans = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I7"){
                var valoare_datorii = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I8"){
                var valoare_venituri_in_avans = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I9"){
                var valoare_provizioane = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I10"){
                var valoare_capitaluri_total = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I11"){
                var valoare_capital_subscris_varsat = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I12"){
                var valoare_patrimoniul_regiei = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I13"){
                var valoare_cifra_de_afacere_neta = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I14"){
                var valoare_venituri_totale = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I15"){
                var valoare_cheltuieli_totale = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I16"){
                var valoare_profit_brut = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I17"){
                var valoare_pierdere_bruta = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I18"){
                var valoare_profit_net = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I19"){
                var valoare_pierdere_neta = indicatori[z].val_indicator;
              } else if(indicatori[z].indicator === "I20"){
                var valoare_numar_salariati = indicatori[z].val_indicator;
              } else {Console.log("Error")};
              
              connection.query("INSERT INTO Date_Financiare(CUI,AN,Numar_Salariati,Pierdere_Neta,Profit_Net,Pierdere_Bruta,Profit_Brut,Cheltuieli_Totale,Venituri_Totale,Cifra_De_Afacere_Neta,Patrimoniul_Regiei,Capital_Subscris_Varsat,Capitaluri_Total,Provizioane,Venituri_In_Avans,Datorii,Cheltuieli_In_Avans,Casa_Si_Conturi,Creante,Stocuri,Active_Circulante,Active_Mobilizate,CAEN) VALUES ("+cuiul+","+anul+","+valoare_numar_salariati+","+valoare_pierdere_neta+","+valoare_profit_net+","+valoare_pierdere_bruta+","+valoare_profit_brut+","+valoare_cheltuieli_totale+","+valoare_venituri_totale+","+valoare_cifra_de_afacere_neta+","+valoare_patrimoniul_regiei+","+valoare_capital_subscris_varsat+","+valoare_capitaluri_total+","+valoare_provizioane+","+valoare_venituri_in_avans+","+valoare_datorii+","+valoare_cheltuieli_in_avans+","+valoare_casa_si_conturi+","+valoare_creante+","+valoare_stocuri+","+valoare_active_circulante+","+valoare_active_mobilizate+","+caen+");", function(error,results,fields) {
                if (error) { /*return console.error(error.message);*/}
              })
            }
          }
        })
    }
  });
});

数据库查询结果示例

[
  RowDataPacket {
    CUI: 2147483647,
    Denumire_Companie: 'Company Name 1',
    Reg_Com: 'F40/2569/2003',
    Status: 1048,
    Localitate: null
  },
  RowDataPacket {
    CUI: 45892171,
    Denumire_Companie: 'Company Name 2',
    Reg_Com: 'J6/342/2022',
    Status: 1048,
    Localitate: 'Municipiul Bistriţa'
  },
  RowDataPacket {
    CUI: 45892163,
    Denumire_Companie: 'Company Name 3',
    Reg_Com: 'J6/340/2022',
    Status: 1048,
    Localitate: 'Loc. Viişoara'
  }
]

问题原因分析

变量未定义：在无财务数据的分支中，cuiul和anul两个变量未被初始化，仅在有数据的else分支里定义。拼接SQL时会生成VALUES (undefined, undefined, 0,...)的无效语句，触发SQL语法错误。
错误被屏蔽：插入操作的错误处理代码被注释，导致无法看到具体报错信息，无法直接定位问题。
循环变量冲突：外层遍历公司的循环和内层遍历年份的循环都使用变量i，会导致变量覆盖，异步操作中可能获取错误的年份或公司数据。
SQL拼接风险：直接将变量拼接到SQL字符串中，既容易出现语法错误，还存在SQL注入安全隐患。

修复方案

1. 补全无数据分支的变量定义

从当前循环的公司和年份中获取CUI和年份值，无需依赖API返回结果：

// 在无数据分支开头添加变量定义
var cuiul = one_after_another.CUI;
var anul = years_to_scrape[yearIndex]; // 注意修改内层循环变量名

2. 使用参数化查询

用占位符?代替直接拼接变量，传递参数数组，避免语法错误和SQL注入：

// 无数据分支的插入语句改为参数化查询
const insertSql = `INSERT INTO Date_Financiare(CUI,AN,Numar_Salariati,Pierdere_Neta,Profit_Net,Pierdere_Bruta,Profit_Brut,Cheltuieli_Totale,Venituri_Totale,Cifra_De_Afacere_Neta,Patrimoniul_Regiei,Capital_Subscris_Varsat,Capitaluri_Total,Provizioane,Venituri_In_Avans,Datorii,Cheltuieli_In_Avans,Casa_Si_Conturi,Creante,Stocuri,Active_Circulante,Active_Mobilizate,CAEN) VALUES (?, ?, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)`;
connection.query(insertSql, [cuiul, anul], function(error, results, fields) {
  if (error) { return console.error(error.message); } // 取消错误输出注释
  console.log("Inserted into DB");
});

3. 修复循环变量冲突

将内层遍历年份的循环变量从i改为yearIndex，避免覆盖外层循环的i：

for (yearIndex in years_to_scrape){
  // ... 内层循环逻辑
}

4. 启用错误日志

取消错误处理代码的注释，插入失败时能直接在控制台看到错误信息，方便调试。

5. 优化异步流程（可选）

使用async/await替代嵌套回调，让代码更易读，避免异步循环中的变量作用域问题：

// 将外层回调改为async函数
connection.query("SELECT ...", async function(error, results, fields) {
  if (error) { return console.error(error.message); }
  for (const company of db_results) {
    for (const year of years_to_scrape) {
      const url = `https://webservicesp.anaf.ro/bilant?an=${year}&cui=${company.CUI}`;
      const res = await fetch(url);
      const json = await res.json();
      const indicatori = json.i || [];
      
      if (indicatori.length === 0) {
        // 插入全0记录
        const insertSql = `INSERT INTO Date_Financiare(CUI,AN,Numar_Salariati,...) VALUES (?, ?, 0,...)`;
        await new Promise((resolve, reject) => {
          connection.query(insertSql, [company.CUI, year], (err) => {
            if (err) reject(err);
            else resolve();
          });
        });
      } else {
        // 处理有数据的情况...
      }
    }
  }
});

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

Node.js脚本无财务数据时无法插入MySQL问题求助

相关代码 #

数据库查询结果示例 #

问题原因分析 #

修复方案 #

1. 补全无数据分支的变量定义 #

2. 使用参数化查询 #

3. 修复循环变量冲突 #

4. 启用错误日志 #

5. 优化异步流程（可选） #