LAST_INSERT_ID() MySQL

问题:

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?

回答1:

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 frm table1

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;   

回答2:

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();

回答3:

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;

回答4:

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 :-)

回答5:

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 
    }

回答6:

For no InnoDB solution: you can use a procedure don\'t forgot to set the 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;

回答7:

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  ) );

回答8:

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)

回答9:

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.

回答10:

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();