Nodejs和SQL"/>
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
发布评论