Retrieve last inserted id with Mysql

Question

Good day,

I am willing to retrieve the id value of a freshly inserted row in Mysql.

I know there is mysqli_insert_id function, but:

I can't specify the table
Maybe there would be a risk of retrieving the wrong id, if a query is made in the meanwhile.
I am using node.js MySQL

I don't want to take the risk to query the highest id since there are a lot of queries, it could give me the wrong one...

(My id column is on auto-increment)

What makes you think that you have to specify the table you've inserted to to get the ID? What have you tried so far to make it work? — Nico Haase, Jun 06 '20 at 19:56

luksch · Accepted Answer · 2020-09-23T06:54:14.430

174

https://github.com/mysqljs/mysql#getting-the-id-of-an-inserted-row describes the solution perfectly well:

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

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

edited Sep 23 '20 at 06:54

answered Jul 12 '15 at 18:30

luksch

11,497
6
38
53

2

What if there are [multiple `insert` statements](//stackoverflow.com/q/39270007/4928642)? – Qwertiy Sep 01 '16 at 11:13
2

What can I do if my primary key is a non-incrementing type? I'm using uuid as a BINARY(16) for the primary key. – Raghav Mehta Dec 21 '16 at 09:01
4

Hi Raghav, if your key is not auto-incrementing and not database created your should not need to retrieve it like this. After all, you should have the key already somewhere in your programming logic. If you struggle, I would suggest you create an independent question about this here on SO. – luksch Dec 24 '16 at 13:09
4

@luksch uuid() is a function of mysql, created on the database, he doesn't know what the uuid is until the insert into statement is called. – mattdevio Feb 01 '17 at 00:26
when i use console.log(result.insertId). It work good. But when send response.send(result.insertId)it throws errors. Why it is? – Sweety Jun 13 '17 at 09:31
what if I'm working with stored procedures, my insertedId is always 0... – André Dos Santos Jun 25 '17 at 00:47
It's been a while since this was posted but I am having the same issue. In my case `console.log(result.insertId);` returns undefined. Could you please help me as well? – Valamorde Jan 24 '18 at 17:48
@Valamorde I currently do not use nodejs any more, so I would suggest you create a new question here describing exactly what the problem is. My guess is, that your table definition uses AUTO_INCREMENT in the id column, but your primary key is a composite key? see https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html – luksch Jan 25 '18 at 08:09
@valamorde You have to `SELECT LAST_INSERT_ID()` – Theo Emms Sep 08 '19 at 21:45
@Qwertiy: if you have multiple insert statements you need to retrieve the last record ID after each insert statement – Nello Castellano Apr 13 '21 at 03:25
I am trying to resolve() a Promise with the insertId and it gives me node:internal/validators:119 TypeError [ERR_INVALID_ARG_TYPE]: The "path" argument must be of type string. Received type number (4) – Nagusameta Nov 21 '21 at 08:59

score 12 · Answer 2 · answered Aug 11 '17 at 12:58

var table_data =  {title: 'test'};

connection_db.query('INSERT INTO tablename SET ?', table_data , function(err, result, fields) {
  if (err) {
      // handle error
    }else{
       // Your row is inserted you can view  
      console.log(result.insertId);
    }
});

You can also view by visiting this link https://github.com/mysqljs/mysql#getting-the-id-of-an-inserted-row

score -2 · Answer 3 · edited May 09 '17 at 12:31

-2

I think you misunderstood the use of mysqli_insert_id() method. This method must be executed immediately after the insert statement to obtain the last inserted id. if you do it my MySQL directly

INSERT INTO(a,b)values(1,'test');
SELECT LAST_INSERT_ID();  -- this will display the last inserted id

edited May 09 '17 at 12:31

derM - not here for BOT dreams

13,081
4
49
89

answered Jul 12 '15 at 18:53

Jaylen

39,043
40
128
221

Retrieve last inserted id with Mysql

3 Answers3

Linked