LAST_INSERT_ID() MySQL

Question

I have a MySQL question that I think must be quite easy. I need to return the LAST INSERTED ID from table1 when I run the following MySql query:

INSERT INTO table1 (title,userid) VALUES ('test',1); 
INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(),4,1);
SELECT LAST_INSERT_ID();

As you can understand the current code will just return the LAST INSERT ID of table2 instead of table1, how can I get the id from table1 even if I insert into table2 between?

score 296 · Accepted Answer · edited Jan 26 '22 at 22:41

296

You could store the last insert id in a variable :

INSERT INTO table1 (title,userid) VALUES ('test', 1); 
SET @last_id_in_table1 = LAST_INSERT_ID();
INSERT INTO table2 (parentid,otherid,userid) VALUES (@last_id_in_table1, 4, 1);

Or get the max id from table1 (EDIT: Warning. See note in comments from Rob Starling about possible errors from race conditions when using the max id)

INSERT INTO table1 (title,userid) VALUES ('test', 1); 
INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(), 4, 1); 
SELECT MAX(id) FROM table1;

(Warning: as Rob Starling points out in the comments)

edited Jan 26 '22 at 22:41

Wosi

41,986
17
75
82

answered Oct 01 '10 at 09:39

Julien Hoarau

48,964
20
128
117

6

Thanks! I didn't get it working first as I was using asp.net with MySQL and needed to add Allow User Variables=True to the Connection String to allow variables. – Martin Oct 01 '10 at 10:19
135

max is not a good idea, as you could lose a race with another inserter. – Rob Starling Jan 17 '13 at 03:38
5

@RobStarling I totally agree, but in case somebody absolutely needs it; use a transaction with the proper isolation level. – Aidiakapi Mar 11 '13 at 14:07
8

what if 2 INSERTS happend at the same time or one before another? – FosAvance Mar 28 '13 at 16:45
41

@FosAvance LAST_INSERT_ID() is connection specific[ref](http://stackoverflow.com/a/14827987/1363495) – Gun2sh Dec 31 '13 at 17:20
#Julien Hoarau what is the difference between SET @last_id_in_table1 = and SET last_id_in_table1 = ? – Jun 06 '15 at 13:50
1

Using MAX() is not safe outside a transaction (i.e. autocommit enabled) and inside a transaction with a transaction isolation level below serializable. See: https://en.wikipedia.org/wiki/Isolation_(database_systems) – snorbi Oct 14 '16 at 14:43
It would be possible to save the @last_id_in_table1 variable into a php variable to use it later? – Angel Apr 27 '17 at 13:27
It would be possible to save the last_id_in_table1 variable into a php variable to use it later? With this last_id I need to attach some records in another table with this last_id, so I need: 1) Do an INSERT and get the last_id_in_table1 INSERT into Table1(name) values ("AAA"); SET last_id_in_table1 = LAST_INSERT_ID(); 2) For any indeterminated rows in another table, UPDATING these rows with the last_id_insert generated in the insert. foreach (element as e){ UPDATE Table2 SET column1 = last_id_in_table1 WHERE id in array() } – Angel Apr 27 '17 at 13:37
You should probably not use `LAST_INSERT_ID()` after `INSERT IGNORE`. If there is a key collision, the new row will not be inserted and `LAST_INSERT_ID()` will return 0. – Ben Oct 19 '17 at 15:14
Not safe, use transaction on InnoDB engine. Or use ``LOCK TABLES`` see my post: https://stackoverflow.com/a/51506539/2935383 – raiserle Jul 24 '18 at 19:55

score 25 · Answer 2 · answered Jun 07 '13 at 08:43

Since you actually stored the previous LAST_INSERT_ID() into the second table, you can get it from there:

INSERT INTO table1 (title,userid) VALUES ('test',1); 
INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(),4,1);
SELECT parentid FROM table2 WHERE id = LAST_INSERT_ID();

score 15 · Answer 3 · answered Apr 01 '13 at 13:29

15

This enables you to insert a row into 2 different tables and creates a reference to both tables too.

START TRANSACTION;
INSERT INTO accounttable(account_username) 
    VALUES('AnAccountName');
INSERT INTO profiletable(profile_account_id) 
    VALUES ((SELECT account_id FROM accounttable WHERE account_username='AnAccountName'));
    SET @profile_id = LAST_INSERT_ID(); 
UPDATE accounttable SET `account_profile_id` = @profile_id;
COMMIT;

answered Apr 01 '13 at 13:29

Ospho

2,756
5
26
39

1

Good shot on the transaction. – Kurt Van den Branden Dec 14 '16 at 22:42
1

But: only available on InnoDB. – raiserle Jul 24 '18 at 19:29

score 6 · Answer 4 · answered May 15 '17 at 10:41

I had the same problem in bash and i'm doing something like this:

mysql -D "dbname" -e "insert into table1 (myvalue) values ('${foo}');"

which works fine:-) But

mysql -D "dbname" -e "insert into table1 (myvalue) values ('${foo}');set @last_insert_id = LAST_INSERT_ID();"
mysql -D "dbname" -e "insert into table2 (id_tab1) values (@last_insert_id);"

don't work. Because after the first command, the shell will be logged out from mysql and logged in again for the second command, and then the variable @last_insert_id isn't set anymore. My solution is:

lastinsertid=$(mysql -B -N -D "dbname" -e "insert into table1 (myvalue) values ('${foo}');select LAST_INSERT_ID();")
mysql -D "dbname" -e "insert into table2 (id_tab1) values (${lastinsertid});"

Maybe someone is searching for a solution an bash :-)

``LAST_INSERT_ID`` only available for the active connection. ``#mysq``-command connect for every command. You can connect with the ``mysql`` command to the server and insert your queries, or you can create a text file and you the ``mysql`` command with redirect from txt file aka ``mysql [connection option] < txt.file`` — raiserle, Jul 24 '18 at 19:31

score 1 · Answer 5 · answered Feb 08 '17 at 17:59

1

We only have one person entering records, so I execute the following query immediately following the insert:

$result = $conn->query("SELECT * FROM corex ORDER BY id DESC LIMIT 1");

while ($row = $result->fetch_assoc()) {

            $id = $row['id'];

}

This retrieves the last id from the database.

answered Feb 08 '17 at 17:59

sspence65

125
1
6

1

I had to give a valid example, so I used the one I use. If I had done it in C, you would probably have complained "not every one uses C" .... – sspence65 Mar 31 '17 at 20:49
Probably the safest and simplest out of all ;) – Anjana Silva Jul 11 '20 at 10:46

score 1 · Answer 6 · answered Apr 27 '17 at 13:39

It would be possible to save the last_id_in_table1 variable into a php variable to use it later?

With this last_id I need to attach some records in another table with this last_id, so I need:

1) Do an INSERT and get the last_id_in_table1

INSERT into Table1(name) values ("AAA"); 
SET @last_id_in_table1 = LAST_INSERT_ID();

2) For any indeterminated rows in another table, UPDATING these rows with the last_id_insert generated in the insert.

$element = array(some ids)    
foreach ($element as $e){ 
         UPDATE Table2 SET column1 = @last_id_in_table1 WHERE id = $e 
    }

score 1 · Answer 7 · edited Aug 14 '20 at 16:27

For no InnoDB solution: you can use a procedure don't forgot to set the delimiter for storing the procedure with ;

CREATE PROCEDURE myproc(OUT id INT, IN otherid INT, IN title VARCHAR(255))
BEGIN
LOCK TABLES `table1` WRITE;
INSERT INTO `table1` ( `title` ) VALUES ( @title ); 
SET @id = LAST_INSERT_ID();
UNLOCK TABLES;
INSERT INTO `table2` ( `parentid`, `otherid`, `userid` ) VALUES (@id, @otherid, 1); 
END

And you can use it...

SET @myid;
CALL myproc( @myid, 1, "my title" );
SELECT @myid;

score 1 · Answer 8 · edited May 02 '20 at 05:49

1

Instead of this LAST_INSERT_ID() try to use this one

mysqli_insert_id(connection)

edited May 02 '20 at 05:49

David Buck

3,752
35
31
35

answered May 02 '20 at 02:41

Sambit Kumar Dalai

19
2

score 1 · Answer 9 · answered Mar 09 '21 at 13:00

In trigger BEFORE_INSERT this working for me:

SET @Last_Insrt_Id = (SELECT(AUTO_INCREMENT /*-1*/) /*as  Last_Insert_Id*/ 
FROM information_schema.tables 
WHERE table_name = 'tblTableName' AND table_schema = 'schSchemaName');

Or in simple select:

SELECT(AUTO_INCREMENT /*-1*/) as Last_Insert_Id
FROM information_schema.tables 
WHERE table_name = 'tblTableName' AND table_schema = 'schSchemaName');

If you want, remove the comment /*-1*/ and test in other cases. For multiple use, I can write a function. It's easy.

score 0 · Answer 10 · edited Mar 04 '14 at 13:02

0

For last and second last:

INSERT INTO `t_parent_user`(`u_id`, `p_id`) VALUES ((SELECT MAX(u_id-1) FROM user) ,(SELECT MAX(u_id) FROM user  ) );

edited Mar 04 '14 at 13:02

Roman C

49,761
33
66
176

answered Mar 04 '14 at 12:44

user2541471

11

This solution is not safe outside a transaction (i.e. autocommit enabled) and inside a transaction with a transaction isolation level below serializable. See: https://en.wikipedia.org/wiki/Isolation_(database_systems) – snorbi Oct 14 '16 at 14:40

score 0 · Answer 11 · answered Aug 07 '21 at 05:49

We could also use $conn->insert_id; // Create connection

    $conn = new mysqli($servername, $username, $password, $dbname);
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";

    if ($conn->query($sql) === TRUE) {
        $last_id = $conn->insert_id;
        echo "New record created successfully. Last inserted ID is: " . $last_id;
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

Alexander Suarez · Answer 12 · 2021-09-19T04:57:06.483

My code does not work for me. Any idea to recover the id of my last insert this is my code I am new developing and I do not know much

I GOT ERROR IN THE QUERY AND I DON'T KNOW HOW TO SEND PRINT IN THE LINE OF $ session-> msg ('s', "Product added successfully. Make cost configuration". LAST_INSERT_ID ());

ALREADY VERIFY AND IT IS CORRECT THE CONNECTION AND THE FIELDS OF THE DATABASE.

<?php
 if(isset($_POST['add_producto'])){
  $req_fields = array( 'nombre', 'categoria', 'proveedor');
   validate_fields($req_fields);
   if(empty($errors)){
     $codigobarras  = remove_junk($db->escape($_POST['codigobarras']));
     $identificador   = remove_junk($db->escape($_POST['identificador']));
     $nombre   = remove_junk($db->escape($_POST['nombre']));
     $categoria   =  (int)$db->escape($_POST['categoria']);
     $etiquetas   =  remove_junk($db->escape($_POST['etiquetas']));
     $unidadmedida   =  remove_junk($db->escape($_POST['unidadmedida']));
     $proveedor   =  remove_junk($db->escape($_POST['proveedor']));
     $fabricante   =  remove_junk($db->escape($_POST['idfabricante']));
     $maximo   =  remove_junk($db->escape($_POST['maximo']));
     $minimo   =  remove_junk($db->escape($_POST['minimo']));
     $descripcion   =  remove_junk($db->escape($_POST['descripcion']));
     $dias_vencimiento   =  remove_junk($db->escape($_POST['dias_vencimiento']));
      
     $servicio   = "0";
      if (isset($_POST['servicio'])){
        $servicio =implode($_POST['servicio']);
     }
     $numeroserie   = "0"; 
      if (isset($_POST['numeroserie'])){
        $numeroserie =implode($_POST['numeroserie']);
     }

     $ingrediente   =  "0";
      if (isset($_POST['ingrediente'])){
        $ingrediente =implode($_POST['ingrediente']);
     }

     $date    = make_date();
     $query  = "INSERT INTO productos (";
     $query .=" codigo_barras,identificador_producto,nombre,idcategoria,idetiquetas,unidad_medida,idproveedor,idfabricante,max_productos,min_productos,descripcion,dias_vencimiento,servicio,numero_serie,ingrediente,activo";
     $query .=") VALUES (";
     $query .=" '{$codigobarras}', '{$identificador}', '{$nombre}', '{$categoria}', '{$etiquetas}', '{$unidadmedida}', '{$proveedor}', '{$fabricante}', '{$maximo}', '{$minimo}', '{$descripcion}', '{$dias_vencimiento}', '{$servicio}', '{$numeroserie}', '{$ingrediente}', '1'";
     $query .=");";
     $query .="SELECT LAST_INSERT_ID();";

     if($db->query($query)){
      $session->msg('s',"Producto agregado exitosamente. Realizar configuracion de costos" . LAST_INSERT_ID());
       redirect('precio_producto.php', false);
     } else {
       $session->msg('d',' Lo siento, registro falló.');
       redirect('informacion_producto.php', false);
     }
   } else{
     $session->msg("d", $errors);
     redirect('informacion_producto.php',false);
   }
 }
?>

Hi @Alexander Suarez and welcome to StackOverflow. Could you please use english? — captain-yossarian from Ukraine, Sep 16 '21 at 19:37
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-ask). — Community, Sep 16 '21 at 22:34
I have made the language change to English. I hope you can help me with my code — Alexander Suarez, Sep 19 '21 at 04:58

score -2 · Answer 13 · answered Oct 15 '14 at 22:15

-2

Just to add for Rodrigo post, instead of LAST_INSERT_ID() in query you can use SELECT MAX(id) FROM table1;, but you must use (),

INSERT INTO table1 (title,userid) VALUES ('test', 1)
INSERT INTO table2 (parentid,otherid,userid) VALUES ( (SELECT MAX(id) FROM table1), 4, 1)

answered Oct 15 '14 at 22:15

Pavlen

129
1
12

This solution is not safe outside a transaction (i.e. autocommit enabled) and inside a transaction with a transaction isolation level below serializable. See: https://en.wikipedia.org/wiki/Isolation_(database_systems) – snorbi Oct 14 '16 at 14:39

score -9 · Answer 14 · edited May 11 '16 at 06:57

-9

If you need to have from mysql, after your query, the last auto-incremental id without another query, put in your code:

mysql_insert_id();

edited May 11 '16 at 06:57

Thamilhan

13,040
5
37
59

answered Jun 05 '13 at 20:08

Luca Perico

19

15

Don't recommend mysql_* - those are deprecated – German Rumm Aug 05 '13 at 08:12
[cletus](http://stackoverflow.com/users/18393/cletus) got 106 upvotes and marked answer for **mysql_insert_id** here: http://stackoverflow.com/a/897361/153923 – Dec 13 '13 at 20:22
2

cletus answeed it in 2009! mysql is deprecated better to use mysqli and mysqli_insert_id http://ie2.php.net/mysqli_insert_id – Maciej Paprocki Jan 10 '14 at 15:39
2

Luca, If you'll delete this outdated answer, I believe your reputation points will be returned. – TecBrat Apr 21 '14 at 18:19
Another valid alternative to the deprecated suggestion is PDO::lastInsertId (http://php.net/manual/en/pdo.lastinsertid.php) – w5m Nov 21 '14 at 09:49

LAST_INSERT_ID() MySQL

14 Answers14

Linked

Related