最近在寫學生申報項目的後端接口時發現需要獲取數據庫自動生成的主鍵，因為要先將項目信息插入到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});
});
})
})
})
