How to Reliably Retrieve the Last Inserted ID in MySQL with Node.js?

Retrieving Last Inserted ID with MySQL and Node.js

In MySQL, obtaining the ID of a newly inserted row is a common operation. However, relying solely on the mysqli_insert_id function can pose limitations. To address these concerns for developers using Node.js MySQL:

Limitations of mysqli_insert_id:

• Unable to Specify Table: The function doesn't allow specifying the table where the row was inserted.
• Potential for Wrong ID: If another query inserts a row before the mysqli_insert_id function is called, there's a risk of retrieving the wrong ID.

Solution:

The recommended solution is to use the MySQL driver's insertId property, which provides a reliable method for retrieving the last inserted ID. Here's an example:

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'username',
  password: 'password',
  database: 'database_name',
});

connection.query('INSERT INTO posts SET ?', { title: 'test' }, (err, result, fields) => {
  if (err) throw err;

  console.log(result.insertId);
});

Benefits of this Approach:

• Targeted ID Retrieval: It ensures you retrieve the ID for the specific table and row you inserted.
• Concurrency Avoidance: By executing the query to retrieve the ID immediately after the insertion, you eliminate the risk of other queries interfering.
• Accurate Result: The insertId property provides a reliable and accurate way to obtain the ID of the last inserted row.

The above is the detailed content of How to Reliably Retrieve the Last Inserted ID in MySQL with Node.js?. For more information, please follow other related articles on the PHP Chinese website!