使用Sequelize更新表时遭遇Unknown column 'NaN'错误求助

我帮你分析下这个问题，你遇到的Unknown column 'NaN' in 'field list'错误，本质是因为传给Sequelize的更新参数里出现了NaN值，MySQL会把这个NaN误当成列名解析，所以才抛出了这个异常。

先看你代码里几个高风险的地方：

• 计算字段short_recovery: recovery_amount - net_recoverable_after_tds：如果recovery_amount或net_recoverable_after_tds是undefined、null，或者不是有效数字类型，减法运算就会得到NaN。
• 计算字段recovery_period: dayDiff(recovery_date, actual_billing_date )：如果recovery_date或actual_billing_date是无效日期（比如未赋值、格式错误），dayDiff函数大概率会返回NaN。
• 还有actual_billing_date、bill_amount、tax_rate这些未在代码片段里展示赋值逻辑的变量，如果它们未定义或赋值出错，也会导致最终值为NaN。

另外，你代码里有个小拼写错误：milestone_blling_amount应该是milestone_billing_amount，虽然这不是当前报错的直接原因，但也建议修正，避免后续出现其他问题。

解决方案 #

• 排查所有计算字段的来源值：
  • 确保recovery_amount、net_recoverable_after_tds都是有效数字，可通过类型转换+默认值避免NaN：

    'short_recovery': (Number(recovery_amount) || 0) - (Number(net_recoverable_after_tds) || 0)
    

  • 检查dayDiff函数的实现，确保传入的日期参数是合法的Date对象，且函数处理了无效日期的情况：

    function dayDiff(date1, date2) {
      const d1 = new Date(date1);
      const d2 = new Date(date2);
      if (isNaN(d1.getTime()) || isNaN(d2.getTime())) {
        return 0; // 或根据业务需求设置其他默认值
      }
      return Math.abs(Math.floor((d2 - d1) / (1000 * 60 * 60 * 24)));
    }
    

• 校验未展示的变量赋值：比如actual_billing_date、bill_amount等变量，确保它们都被正确赋值，无undefined或非预期值。如果是前端传参，要做类型校验和转换。
• 添加NaN前置过滤：在调用update前，检查所有字段值，替换NaN为合法值：

  const updateData = {
    'poregisterReference': String(req.body.reference),
    'po_number' : req.body.po_number,
    'billing_milestone': String(req.body.billing_milestone),
    'billing_milestone_probable_date':checkDate(req.body.billing_milestone_probable_date),
    'milestone_billing_amount':req.body.milestone_billing_amount, // 修正拼写错误
    'actual_billing_date': actual_billing_date,
    'bill_number':req.body.bill_number,
    'bill_amount':bill_amount,
    'tax_rate':tax_rate,
    'tds_rate':tds_rate,
    'recovery_date': recovery_date,
    'recovery_amount':recovery_amount ,
    'utr_number': String(req.body.utr_number),
    'bill_amount_with_tax': bill_amount_with_tax,
    'net_recoverable_after_tds': net_recoverable_after_tds ,
    'short_recovery': (Number(recovery_amount) || 0) - (Number(net_recoverable_after_tds) || 0),
    'recovery_period': dayDiff(recovery_date, actual_billing_date )
  };
  
  // 替换NaN为null或0（根据数据库字段规则调整）
  Object.keys(updateData).forEach(key => {
    if (Number.isNaN(updateData[key])) {
      updateData[key] = null;
    }
  });
  
  result.update(updateData);
  

你提供的代码与错误堆栈 #

你的更新代码：

db.billingplan.findById(req.params.id).then(result=>{
 result.update({
 'poregisterReference': String(req.body.reference),
 'po_number' : req.body.po_number,
 'billing_milestone': String(req.body.billing_milestone),
 'billing_milestone_probable_date':checkDate(req.body.billing_milestone_probable_date),
 'milestone_blling_amount':req.body.milestone_billing_amount,
 'actual_billing_date': actual_billing_date,
 'bill_number':req.body.bill_number,
 'bill_amount':bill_amount,
 'tax_rate':tax_rate,
 'tds_rate':tds_rate,
 'recovery_date': recovery_date,
 'recovery_amount':recovery_amount ,
 'utr_number': String(req.body.utr_number),
 'bill_amount_with_tax': bill_amount_with_tax,
 'net_recoverable_after_tds': net_recoverable_after_tds ,
 'short_recovery': recovery_amount - net_recoverable_after_tds,
 'recovery_period': dayDiff(recovery_date, actual_billing_date )
 });
});

错误堆栈：

Unhandled rejection SequelizeDatabaseError: Unknown column 'NaN' in 'field list'
at Query.formatError (D:\Users\Temp\Desktop\POregister\node_modules\sequelize\lib\dialects\mysql\query.js:228:16)
at Query.connection.query [as onResult] (D:\Users\Temp\Desktop\POregister\node_modules\sequelize\lib\dialects\mysql\query.js:55:23)
at Query.Command.execute (D:\Users\Temp\Desktop\POregister\node_modules\mysql2\lib\commands\command.js:30:12)
at Connection.handlePacket (D:\Users\Temp\Desktop\POregister\node_modules\mysql2\lib\connection.js:515:28)
at PacketParser.onPacket (D:\Users\Temp\Desktop\POregister\node_modules\mysql2\lib\connection.js:94:16)
at PacketParser.executeStart (D:\Users\Temp\Desktop\POregister\node_modules\mysql2\lib\packet_parser.js:77:14)
at Socket. (D:\Users\Temp\Desktop\POregister\node_modules\mysql2\lib\connection.js:102:29)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:547:20)

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