Node.js+MySQL：HTML表单数据从内存数据库迁移至本地MySQL时遭遇SQL语法错误问题求助

Hey there! Let's get that insert query working properly. The syntax error you're seeing happens because you're passing an object directly to the VALUES ? placeholder, which the MySQL module doesn't handle the way you expect—it's converting the object into a key-value string that doesn't fit the VALUES syntax.

Why the Error Occurs #

Your userData is an object like { title: 'ms', firstname: 'Tina', ... }, but when you use VALUES ? with this object, the module tries to insert it as VALUES title = 'ms', firstname = 'Tina'...—which is invalid SQL, since VALUES expects parentheses-wrapped value lists.

Solution 1: Use SET ? (Simplest for Object Data) #

Since your userData object's keys match your personal table's column names perfectly, you can use the SET ? syntax instead. This lets the MySQL module automatically map the object's key-value pairs to the table's columns:

// Inside your request.on("end") callback, after parsing userData
con.query("INSERT INTO personal SET ?", userData, function(err, result) {
  if (err) {
    console.error("Insert error:", err);
    // Add proper error handling here (e.g., send error response to client)
    return;
  }
  console.log("1 record inserted, ID:", result.insertId);
});

Solution 2: Explicit Value Array with VALUES #

If you prefer to stick with the VALUES syntax, you need to pass an array of values in the exact order of your columns:

// Inside your request.on("end") callback, after parsing userData
const values = [
  userData.title,
  userData.firstname,
  userData.surname,
  userData.mobile,
  userData.email
];

con.query(
  "INSERT INTO personal (title, firstname, surname, mobile, email) VALUES (?, ?, ?, ?, ?)",
  values,
  function(err, result) {
    if (err) {
      console.error("Insert error:", err);
      return;
    }
    console.log("1 record inserted, ID:", result.insertId);
  }
);

Important Notes #

• Double-check that the keys in userData exactly match the column names in your personal table (case sensitivity depends on your database configuration).
• Avoid using throw err in production code—it will crash your server. Instead, log the error and send an appropriate response to the client.
• Make sure your insert query is executed inside the request.on("end") callback—this ensures userData is fully parsed before you try to insert it.

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