如何在PHP中获得MySQL表的最后插入ID ?

I have a table into which new data is frequently inserted. I need to get the very last ID of the table. How can I do this?

我有一个经常插入新数据的表。我需要得到表的最后一个ID。我该怎么做呢?

Is it similar to SELECT MAX(id) FROM table?

是否类似于从表中选择MAX(id) ?

22 个解决方案

#1

If you're using PDO, use PDO::lastInsertId.

如果您正在使用PDO，请使用PDO::lastInsertId。

If you're using Mysqli, use mysqli::$insert_id.

如果您正在使用Mysqli，请使用Mysqli:$insert_id。

If you're still using Mysql:

如果你还在使用Mysql:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

请不要在新代码中使用mysql_*函数。它们不再被维护，并被正式弃用。看看红色的盒子吗?学习准备好的语句，并使用PDO或MySQLi—本文将帮助您决定使用哪个。如果您选择PDO，这里有一个很好的教程。

_{But if you have to, use mysql_insert_id.}

但是如果需要，可以使用mysql_insert_id。

#2

there is a function to know what was the last id inserted in the current connection

有一个函数可以知道在当前连接中插入的最后一个id是什么

mysql_query('INSERT INTO FOO(a) VALUES(\'b\')');
$id = mysql_insert_id();

plus using max is a bad idea because it could lead to problems if your code is used at same time in two different sessions.

另外，使用max是一个坏主意，因为如果您的代码同时在两个不同的会话中使用，可能会导致问题。

That function is called mysql_insert_id

这个函数叫做mysql_insert_id

#3

It's ok. Also you can use LAST_INSERT_ID()

没关系。还可以使用LAST_INSERT_ID()

#4

With PDO:

PDO:

$pdo->lastInsertId();

With Mysqli:

Mysqli:

$mysqli->insert_id;

---

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

请不要在新代码中使用mysql_*函数。它们不再被维护，并被正式弃用。看看红色的盒子吗?学习准备好的语句，并使用PDO或MySQLi—本文将帮助您决定使用哪个。如果您选择PDO，这里有一个很好的教程。

#5

Use mysql_insert_id() function.

使用mysql_insert_id()函数。

See similar question here

看到类似的问题

#6

What you wrote would get you the greatest id assuming they were unique and auto-incremented that would be fine assuming you are okay with inviting concurrency issues.
Since you're using MySQL as your database, there is the specific function LAST_INSERT_ID() which only works on the current connection that did the insert.
PHP offers a specific function for that too called mysql_insert_id.

假设它们是唯一的并且是自动递增的，那么你所写的将会得到最大的id，如果你对并发性问题没有意见的话。因为使用MySQL作为数据库，所以有一个特定的函数LAST_INSERT_ID()，它只在执行插入的当前连接上工作。PHP也为此提供了一个特定函数mysql_insert_id。

#7

You can get the latest inserted id by the in built php function mysql_insert_id();

可以通过内置的php函数mysql_insert_id()获得最新插入的id;

$id = mysql_insert_id();

you an also get the latest id by

您还可以获得最新的id

$id = last_insert_id();

#8

To get last inserted id in codeigniter After executing insert query just use one function called insert_id() on database, it will return last inserted id

要在执行插入查询之后在codeigniter中获得最后插入的id，只需在数据库上使用名为insert_id()的函数，它将返回最后插入的id

Ex:

例:

$this->db->insert('mytable',$data);
echo $this->db->insert_id(); //returns last inserted id

in one line

在一行

echo $this->db->insert('mytable',$data)->insert_id();

#9

It's ok to use mysql_insert_id(), but there is one specific note about using it, you must call it after executed INSERT query, means in the same script session. If you use it otherwise it wouldn't work correctly.

使用mysql_insert_id()是可以的，但是有一个关于使用它的特别说明，您必须在执行插入查询之后调用它，这意味着在同一个脚本会话中。如果你不使用它，它就不能正常工作。

#10

Try this should work fine:

试试这个应该没问题:

$link = mysqli_connect("localhost", "my_user", "my_password", "world");

$query = "INSERT blah blah blah...";
$result = mysqli_query($link, $query);

echo mysqli_insert_id($link);

#11

NOTE: if you do multiple inserts with one statement mysqli::insert_id will not be correct.

注意:如果使用一个语句进行多个插入，那么sqmyli:insert_id将不正确。

The table:

表:

create table xyz (id int(11) auto_increment, name varchar(255), primary key(id));

创建表xyz (id int(11) auto_increment，名称varchar(255)，主键(id);

Now if you do:

现在,如果你做的事:

insert into xyz (name) values('one'),('two'),('three');

插入xyz(名字)值(“一”),(2),(3);

The mysqli::insert_id will be 1 not 3.

insert_id将是1而不是3。

To get the correct value do:

要得到正确的值，请做:

mysqli::insert_id + mysqli::affected_rows) - 1

mysqli::insert_id + mysqli::affected_rows) - 1

This has been document but it is a bit obscure.

这是一份文件，但有点晦涩。

#12

I prefer use a pure MySQL syntax to get last auto_increment id of the table I want.

我更喜欢使用纯MySQL语法来获取我想要的表的最后一个auto_increment id。

php mysql_insert_id() and mysql last_insert_id() give only last transaction ID.

php mysql_insert_id()和mysql last_insert_id()只给出最后一个事务ID。

If you want last auto_incremented ID of any table in your schema (not only last transaction one), you can use this query

如果您想要模式中任何表的最后一个auto_increment ID(不只是最后一个事务ID)，您可以使用这个查询

SELECT AUTO_INCREMENT FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'my_database' 
    AND TABLE_NAME = 'my_table_name';

That's it.

就是这样。

#13

It's sad not to see any answers with an example.

遗憾的是没有一个例子来回答这个问题。

Using Mysqli::$insert_id:

使用Mysqli::$ insert_id:

$sql="INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3)";
$mysqli->query($sql);
$last_inserted_id=$mysqli->insert_id; // returns last ID

Using PDO::lastInsertId:

使用PDO::lastInsertId:

$sql="INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3)";
$database->query($sql);
$last_inserted_id=$database->lastInsertId(); // returns last ID

#14

Clean and Simple -

干净,简单,

$selectquery="SELECT id FROM tableName ORDER BY id DESC LIMIT 1";
$result = $mysqli->query($selectquery);
$row = $result->fetch_assoc();
echo $row['id'];

#15

Using MySQLi transaction I sometimes wasn't able to get mysqli::$insert_id, because it returned 0. Especially if I was using stored procedures, that executing INSERTs. So there is another way within transaction:

使用MySQLi事务时，我有时无法获得MySQLi:$insert_id，因为它返回0。特别是当我使用存储过程时，执行插入。因此，交易中还有另一种方式:

<?php

function getInsertId(mysqli &$instance, $enforceQuery = false){
    if(!$enforceQuery)return $instance->insert_id;

    $result = $instance->query('SELECT LAST_INSERT_ID();');

    if($instance->errno)return false;

    list($buffer) = $result->fetch_row();

    $result->free();

    unset($result);

    return $buffer;
}

?>

#16

Use mysqli as mysql is depricating

使用mysqli作为mysql是不允许的。

<?php
$mysqli = new mysqli("localhost", "yourUsername", "yourPassword", "yourDB");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
// Conside employee table with id,name,designation
$query = "INSERT INTO myCity VALUES (NULL, 'Ram', 'Developer')";
$mysqli->query($query);

printf ("New Record has id %d.\n", $mysqli->insert_id);

/* close connection */
$mysqli->close();
?>

#17

I tried

我试着

mysqli_insert_id($dbConnectionObj)

mysqli_insert_id(dbConnectionObj美元)

This returns the current connection's last inserted id so if you are managing your connections properly this should work. Worked for me at least.

这将返回当前连接的最后一个插入id，因此如果您正在正确地管理连接，那么这个操作应该是有效的。至少对我有帮助。

#18

Please use PDP and then try this

请使用PDP，然后试试这个

$stmt = $db->prepare("...");
$stmt->execute();
$id = $db->lastInsertId();

#19

$lastid = mysql_insert_id();

lastid美元= mysql_insert_id();

#20

By all this discussion I assume that the reason to check max id is to know what id should be next.. (if my max id is 5 then next will be 5+1=6).

通过所有这些讨论，我假设检查max id的原因是知道接下来应该是什么id。(如果我的最大id是5，那么接下来就是5+1=6)。

>>If this is not the reason, my best apologies

>>如果这不是原因，我最好的道歉。

Case if someone else INSERTs information between your CHECK and INSERT would give you wrong ID.

如果其他人在您的CHECK和INSERT之间插入信息，您可能会得到错误的ID。

So It can be solved if you would create hash that could include timestamp or other unique value.

如果你要创建一个包含时间戳或其他唯一值的哈希，就可以解决这个问题。

Then in the same function you can insert your information with empty values and your hash. That would create ID if you have AUTO_INCRECEMENT selected.

然后，在同一个函数中，您可以使用空值和散列插入信息。如果选择了AUTO_INCRECEMENT，将创建ID。

Then in the same function you would still have your hash and you could look for id with the same hash. And then you could complete populating empty values with mysql UPDATE.

然后在相同的函数中，你仍然会有你的哈希，你可以用相同的哈希查找id。然后你可以用mysql更新来填充空值。

This includes a bit more connections, but it is still a way to do it...

这包括更多的联系，但这仍然是一种方法……

Good luck solving it.

祝你好运解决它。

#21

If your table have AUTO INCREMENT column like UserID,Emp_ID,.. then you can use this query to get last inserted record SELECT * FROM table_name where UserID=(select MAX(UserID)from table_name) In PHP code:

如果您的表具有UserID、Emp_ID等自动增量列，然后，您可以使用此查询获取PHP代码中UserID=(从table_name中选择MAX(UserID))的最后插入记录SELECT *:

$con = mysqli_connect('localhost', 'userid', 'password', 'database_name');
                                if (!$con) {
                                    die('Could not connect: ' . mysqli_error($con));
                                }
                                $sql = "SELECT * FROM table_name where UserID=(select MAX(UserID)from table_name)";
                                $result = mysqli_query($con, $sql);

Then you can use fetched data as your requirement

然后您可以使用获取的数据作为您的需求

#22

mysql_query("INSERT INTO mytable (product) values ('kossu')");

printf("Last inserted record has id %d\n", ***mysql_insert_id()***);