当前位置： 文江博客话题详情

LAST_INSERT_ID() MySQL

发布于 2024-09-25 20:17:07 字数 355 浏览 15 评论 0原文

我有一个 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();

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

原文

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?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（14）

沩ん囻菔务 2024-10-02 20:17:07

您可以将最后一个插入 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);

或者从 table1 获取最大 id（编辑：警告。请参阅 Rob Starling 的注释中有关使用最大 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;

（警告：正如 Rob Starling 指出的那样）在评论中）

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)

回复 原文
神经暖 2024-10-02 20:17:07

由于您实际上将之前的 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();

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();
回复 原文
嗳卜坏 2024-10-02 20:17:07

这使您能够将一行插入到两个不同的表中，并创建对两个表的引用。

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;

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;
回复 原文
岁月染过的梦 2024-10-02 20:17:07

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

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

效果很好:-)但是

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

不起作用。因为在第一个命令之后，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});"

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

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

回复 原文
戏舞 2024-10-02 20:17:07

我们只有一个人输入记录，因此我在插入后立即执行以下查询：

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

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

            $id = $row['id'];

}

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

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.

回复 原文
美人骨 2024-10-02 20:17:07

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

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

1）执行INSERT并获取last_id_in_table1

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

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

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

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 
    }
回复 原文
吹梦到西洲 2024-10-02 20:17:07

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

mysqli_insert_id(connection)

Instead of this LAST_INSERT_ID()
try to use this one

mysqli_insert_id(connection)
回复 原文
苍风燃霜 2024-10-02 20:17:07

对于没有 InnoDB 解决方案：您可以使用过程
不要忘记使用 ; 设置存储过程的分隔符

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

并且您可以使用它......

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

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;
回复 原文
ι不睡觉的鱼゛ 2024-10-02 20:17:07

在触发器 BEFORE_INSERT 中，这对我有用：

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

或者在简单的选择中：

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

如果需要，请删除注释 /*-1*/ 并在其他情况下进行测试。
为了多次使用，我可以编写一个函数。这很容易。

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.

回复 原文
自此以后，行同陌路 2024-10-02 20:17:07

对于最后一个和倒数第二个：

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

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  ) );
回复 原文
风柔一江水 2024-10-02 20:17:07

我们还可以使用 $conn->insert_id;
// 创建连接

    $conn = new mysqli($servername, $username, $password, $dbname);
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', '[email protected]')";

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

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', '[email protected]')";

    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;
    }
回复 原文
叹沉浮 2024-10-02 20:17:07

我的代码对我不起作用。任何想法来恢复我最后插入的ID，这是我的代码，我是新开发的，我不太了解

我在查询中出现错误，并且我不知道如何在 $ session-> 行中发送打印msg('s', "产品添加成功。进行成本配置".LAST_INSERT_ID());

已验证数据库的连接和字段是否正确。

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

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);
   }
 }
?>
回复 原文
三生路 2024-10-02 20:17:07

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

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

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)
回复 原文
风吹过旳痕迹 2024-10-02 20:17:07

如果您需要在查询后从 mysql 获取最后一个自动增量 id 而无需再次查询，请输入您的代码：

mysql_insert_id();

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();
回复 原文
~没有更多了~

关于作者

梦魇绽荼蘼

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

苍风燃霜

文章 0 评论 0

我的黑色迷你裙

文章 0 评论 0

悸初

文章 0 评论 0

撧情箌佬

文章 0 评论 0

森罗

文章 0 评论 0

lyn1245

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文