Nodejs + mysql get the automatically generated id when inserting a record

Nodejs + mysql get the automatically generated id when inserting a record

Recently, when writing the back-end interface of the student reporting project, we found that the primary key automatically generated by the database needs to be obtained, because we need to insert the project information to the project table to obtain the generated project ID, insert the Project ID and instructor ID to the project_member table.

First, you want to use SELECT LAST_INSERT_ID () to obtain the generated ID after the project is inserted. Later, you will find that the automatically generated ID is included in the returned result after the insert operation is complete. Available for rows. insertId

Code:

// Use transactions to insert conn to the project table and project_member table. beginTransaction (function (err) {if (err) {sendData (req, res, next, conn, err);} else {// insert project information conn in the project table. 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 () {// roll back sendData (req, res, next, conn, err) ;});} var insertId = rows. insertId; // obtain the automatically generated id console. log (insertId); // Insert the correspondence between the project and instructor in the member table. query ('insert INTO project_member (project_id, user_id, project_member_role, project_member_task) '+ 'values (' + insertId + ',' + teacherId + ', 2, "instructor ") ', function (err, rows) {if (err) {conn. rollback (function () {// roll back sendData (req, res, next, conn, err) ;});} conn. commit (function (err) {// submit the transaction if (err) {conn. rollback (function () {sendData (req, res, next, conn, err) ;});} console. Log ('success! '); Var data = {status: true, message: "declared successful"} conn. release (); res. send ({"data": data });});})})})