最近在写学生申报项目的后端接口时发现需要获取数据库自动生成的主键，因为要先将项目信息插入到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});
});
})
})
})