最近在寫學生申報項目的後端接口時發現需要擷取資料庫自動生成的主鍵，因為要先将項目資訊插入到project表，得到生成的項目ID，再将項目ID和指導老師ID插入到project_member表
一開始想在插入項目之後，用SELECT LAST_INSERT_ID() 擷取生成的ID，後來發現，在插入操作完成後的傳回結果裡就帶有自動生成的ID。可用rows.insertId擷取
代碼：
//利用事務對project表和project_member表進行插入
conn.beginTransaction(function(err){
if(err){
sendData(req,res,next,conn,err);
}else{//在project表插入項目資訊
conn.query('INSERT INTO project (project_category_id,project_status,project_creator_id,project_name,project_start,' +
'project_end,project_source,project_aid,project_background,project_describe,project_innovation,' +
'project_plan,project_prospect,project_budget,project_resourcerequired)' +
'VALUES ('+category+',0,'+userId+',"'+name+'","'+startTime+'","'+endTime+'",1,"'+aid+'",' +
'"'+background+'","'+describe+'","'+innovation+'","'+plan+'","'+prospect+'","'+budget+'","'+resourcerequired+'")',function(err,rows){
if(err){
conn.rollback(function() {//如果失敗復原
sendData(req,res,next,conn,err);
});
}
var insertId = rows.insertId;//擷取自動生成的id
console.log(insertId);
//在member表中插入項目和指導老師的對應關系
conn.query('INSERT INTO project_member (project_id,user_id,project_member_role,project_member_task) ' +
'VALUES ('+insertId+','+teacherId+',2,"指導老師")',function(err,rows){
if(err){
conn.rollback(function() {//如果失敗復原
sendData(req,res,next,conn,err);
});
}
conn.commit(function(err) {//送出事務
if (err) {
conn.rollback(function() {
sendData(req,res,next,conn,err);
});
}
console.log('success!');
var data = {
status:true,
message : "申報成功"
}conn.release();
res.send({"data":data});
});
})
})
})