NodeJS向MySQL插入JSON数组失败，寻求解决方案

问题描述 #

尝试将JSON数组插入MySQL数据库时触发SQL语法错误，错误信息如下：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''[{"Symbol":"msft","Date":"2022-07-20 00:00:00","Open":259.8999938965,' at line 1

现有代码与表结构 #

数据库表创建语句：

CREATE TABLE IF NOT EXISTS db.AStockHistory 
( Symbol text NOT NULL, 
Date timestamp NOT NULL, 
Open double, 
High double, 
Low double, 
Close double, 
Volume double, 
Dividends double, 
Stock_Splits double );

原有Node.js插入代码：

async function create(stockHistory){
  var data = JSON.stringify(stockHistory);
  const result = await db.query(
    `INSERT INTO db.AStockHistory SET ?`, data); 
  return {message};
}

提交的JSON数组示例：

[
  {
    "Symbol": "msft",
    "Date": "2022-07-20 00:00:00",
    "Open": 259.8999938965,
    "High": 264.8699951172,
    "Low": 258.9100036621,
    "Close": 262.2699890137,
    "Volume": 22788300,
    "Dividends": 0,
    "Stock_Splits": 0
  },
  {
    "Symbol": "msft",
    "Date": "2022-07-21 00:00:00",
    "Open": 259.7900085449,
    "High": 264.8900146484,
    "Low": 257.0299987793,
    "Close": 264.8399963379,
    "Volume": 22404700,
    "Dividends": 0,
    "Stock_Splits": 0
  }
]

错误原因 #

1. INSERT INTO ... SET ? 仅支持单条数据插入，无法直接处理数组类型的多条数据。
2. 对stockHistory执行JSON.stringify是多余操作，数据库驱动会自动处理JS对象/数组的参数绑定，字符串化后反而导致SQL语法结构混乱。

正确实现方案 #

async function create(stockHistory) {
  // 空数组判断，避免报错
  if (!stockHistory || stockHistory.length === 0) {
    return { message: "无数据可插入" };
  }

  // 提取列名（从第一条数据中获取）
  const columns = Object.keys(stockHistory[0]).join(',');
  // 生成每条数据的占位符
  const valuePlaceholders = stockHistory.map(() => 
    `(${Object.keys(stockHistory[0]).map(() => '?').join(',')})`
  ).join(',');
  // 扁平化所有数据值，用于参数绑定
  const flatValues = stockHistory.flatMap(item => Object.values(item));

  const result = await db.query(
    `INSERT INTO db.AStockHistory (${columns}) VALUES ${valuePlaceholders}`,
    flatValues
  );

  return { message: `成功插入${result.affectedRows}条记录` };
}

关键说明 #

• 动态生成SQL结构：根据传入数据的字段自动生成列名和占位符，保证与表结构匹配。
• 参数绑定安全：使用?占位符避免SQL注入，驱动自动处理数据类型转换（如日期字符串转timestamp）。
• 批量插入效率：一次性插入多条数据比循环单条插入性能更高。

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