LAST_INSERT_ID() MySQL

LAST_INSERT_ID() MySQL

提问by Martin

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:

我有一个我认为一定很容易的 MySQL 问题。当我运行以下 MySql 查询时，我需要从 table1 返回 LAST INSERTED ID：

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?

正如您所理解的，当前代码将只返回 table2 而不是 table1 的 LAST INSERT ID，即使我插入 table2 之间，如何从 table1 获取 id？

回答by Julien Hoarau

You could store the last insert id in a variable :

您可以将最后一个插入 ID 存储在一个变量中：

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

或者从 table1 获取最大 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;   

回答by Artem Goutsoul

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

由于您实际上将之前的 LAST_INSERT_ID() 存储到第二个表中，因此您可以从那里获取它：

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

回答by Ospho

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

这使您可以在 2 个不同的表中插入一行并创建对两个表的引用。

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;

回答by Eugen Spinne

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

我在 bash 中遇到了同样的问题，我正在做这样的事情：

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:

不工作。因为在第一个命令之后，shell 将从 mysql 中注销并重新登录第二个命令，然后变量@last_insert_id 不再设置。我的解决办法是：

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

也许有人正在寻找解决方案 bash :-)

回答by raiserle

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

对于没有 InnoDB 解决方案：您可以使用过程 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;

回答by Sambit Kumar Dalai

Instead of this LAST_INSERT_ID()try to use this one

而不是这个LAST_INSERT_ID()尝试使用这个

mysqli_insert_id(connection)

回答by sspence65

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.

这将从数据库中检索最后一个 id。

回答by Angel

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

是否可以将 last_id_in_table1 变量保存到 php 变量中以便以后使用？

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

有了这个last_id，我需要用这个last_id在另一个表中附加一些记录，所以我需要：

1) Do an INSERT and get the last_id_in_table1

1) 执行 INSERT 并获取 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.

2) 对于另一个表中的任何不确定行，使用插入中生成的 last_id_insert 更新这些行。

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

回答by user2541471

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

回答by Pavlen

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

只是为了添加 Rodrigo 帖子，而不是 LAST_INSERT_ID() 在查询中你可以使用 SELECT MAX(id) FROM table1;，但你必须使用 ()，

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