Nodejs和SQL

Error image通过使用多个选择语句将数据插入SQL数据库表user_recipe_consumption中时，我面临错误--throw err; //抛出非MySQL错误^错误：ER_PARSE_ERROR：您的SQL语法有错误；查看与您的MySQL服务器版本相对应的手册，以获取在“蘑菇面食”附近使用的正确语法）； ，'（（从第1行的RecipeEmissions中的RecipeEmissions中选择vegEmission

for (var dataVal = 0; dataVal < req.body.length; dataVal++) {
    var recipeInfo = req.body[dataVal].RecipeName;
    var deviceID = req.body[dataVal].deviceID;
    var totEmission = req.body[dataVal].totalEmission;
    var sql = "INSERT INTO user_recipe_consumption (deviceID, totalEmission, recipeID , vegEmission,date_of_entry) VALUES ('" + deviceID + "','" + totEmission + "', '( select RecipeID, from RecipeEmissions where RecipeName = ?);' , '( select vegEmission from RecipeEmissions where RecipeName = ? );' ,'" + now + "')";              
    con.query(sql, recipeInfo, function(err, result) {
            if (err) throw err;
            console.log("Number of records inserted: " + result.affectedRows);
        });
    }

而不是带有var的sql缩略词（以及与数据类型和sqlinjection有关的问题）您应该基于参数绑定完全使用查询（例如：命名参数）您还应该使用insert select sintax而不是从同一张表中进行多次选择

"INSERT INTO user_recipe_consumption (deviceID, totalEmission, recipeID , vegEmission,date_of_entry) 
SELECT :deviceID, :totEmissino,  RecipeID, vegEmission, :date_of_entry 
FROM RecipeEmissions 
where RecipeName = :RecipeName;"

例如：

connection.execute(
    "INSERT INTO user_recipe_consumption (deviceID, totalEmission, recipeID , vegEmission,date_of_entry) 
    SELECT :deviceID, :totEmission ,  RecipeID, vegEmission, :date_of_entry 
    FROM RecipeEmissions 
    WHERE RecipeName = :RecipeName;",
    {deviceID: deviceID, totEmission: totEmission, date_of_entry:date_of_entry,RecipeName:RecipeName},
function(err, result)
.......

Nodejs和SQL

Error image通过使用多个选择语句将数据插入SQL数据库表user_recipe_consumption中时，我面临错误--throw err; //抛出非MySQL错误^错误：ER_PARSE_ERROR：您的SQL语法有错误；查看与您的MySQL服务器版本相对应的手册，以获取在“蘑菇面食”附近使用的正确语法）； ，'（（从第1行的RecipeEmissions中的RecipeEmissions中选择vegEmission

for (var dataVal = 0; dataVal < req.body.length; dataVal++) {
    var recipeInfo = req.body[dataVal].RecipeName;
    var deviceID = req.body[dataVal].deviceID;
    var totEmission = req.body[dataVal].totalEmission;
    var sql = "INSERT INTO user_recipe_consumption (deviceID, totalEmission, recipeID , vegEmission,date_of_entry) VALUES ('" + deviceID + "','" + totEmission + "', '( select RecipeID, from RecipeEmissions where RecipeName = ?);' , '( select vegEmission from RecipeEmissions where RecipeName = ? );' ,'" + now + "')";              
    con.query(sql, recipeInfo, function(err, result) {
            if (err) throw err;
            console.log("Number of records inserted: " + result.affectedRows);
        });
    }

而不是带有var的sql缩略词（以及与数据类型和sqlinjection有关的问题）您应该基于参数绑定完全使用查询（例如：命名参数）您还应该使用insert select sintax而不是从同一张表中进行多次选择

"INSERT INTO user_recipe_consumption (deviceID, totalEmission, recipeID , vegEmission,date_of_entry) 
SELECT :deviceID, :totEmissino,  RecipeID, vegEmission, :date_of_entry 
FROM RecipeEmissions 
where RecipeName = :RecipeName;"

例如：

connection.execute(
    "INSERT INTO user_recipe_consumption (deviceID, totalEmission, recipeID , vegEmission,date_of_entry) 
    SELECT :deviceID, :totEmission ,  RecipeID, vegEmission, :date_of_entry 
    FROM RecipeEmissions 
    WHERE RecipeName = :RecipeName;",
    {deviceID: deviceID, totEmission: totEmission, date_of_entry:date_of_entry,RecipeName:RecipeName},
function(err, result)
.......