©
本文档使用 PHP中文网手册 发布
(PHP 5)
mysqli::$insert_id -- mysqli_insert_id — Returns the auto generated id used in the last query
面向对象风格
过程化风格
$link
)The mysqli_insert_id() function returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute. If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero.
Note:
Performing an INSERT or UPDATE statement using the LAST_INSERT_ID() function will also modify the value returned by the mysqli_insert_id() function.
link
仅以过程化样式:由 mysqli_connect() 或 mysqli_init() 返回的链接标识。
The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.
Note:
If the number is greater than maximal int value, mysqli_insert_id() will return a string.
Example #1 $mysqli->insert_id example
面向对象风格
<?php
$mysqli = new mysqli ( "localhost" , "my_user" , "my_password" , "world" );
if ( mysqli_connect_errno ()) {
printf ( "Connect failed: %s\n" , mysqli_connect_error ());
exit();
}
$mysqli -> query ( "CREATE TABLE myCity LIKE City" );
$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)" ;
$mysqli -> query ( $query );
printf ( "New Record has id %d.\n" , $mysqli -> insert_id );
$mysqli -> query ( "DROP TABLE myCity" );
$mysqli -> close ();
?>
过程化风格
<?php
$link = mysqli_connect ( "localhost" , "my_user" , "my_password" , "world" );
if ( mysqli_connect_errno ()) {
printf ( "Connect failed: %s\n" , mysqli_connect_error ());
exit();
}
mysqli_query ( $link , "CREATE TABLE myCity LIKE City" );
$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)" ;
mysqli_query ( $link , $query );
printf ( "New Record has id %d.\n" , mysqli_insert_id ( $link ));
mysqli_query ( $link , "DROP TABLE myCity" );
mysqli_close ( $link );
?>
以上例程会输出:
New Record has id 1.
[#1] mail at nikha dot org [2014-01-20 12:37:14]
Hi Dears,
msqli_insert_id() simply does not ALWAYS return the correct value.
Use it only, if you performed some inserts just before. Then you get what you want.
In all other cases: may be, may be not.
I never found out why and why not.
I'm now performing a query like this:
SELECT MAX(`id`) FROM `table`
(by calling mysqli_query() in procedural style, for OO may be similar.)
It' s simple and reliable - if you have set your id colum to "auto-increment". (if not: hm, why not??);
[#2] owenzx at gmail dot com [2013-09-06 02:54:24]
The example is lack of insert_id in multi_query. Here is my example:
Assuming you have a new test_db in mysql like this:
create database if not exists test_db;
use test_db;
create table user_info (_id serial, name varchar(100) not null);
create table house_info (_id serial, address varchar(100) not null);
Then you run a php file like this:
<?php
define('SERVER', '127.0.01');
define('MYSQL_USER', 'your_user_name');
define('MYSQL_PASSWORD', 'your_password');
$db = new mysqli(SERVER, MYSQL_USER, MYSQL_PASSWORD, "test_db", 3306);
if ($db->connect_errno)
echo "create db failed, error is ", $db->connect_error;
else {
$sql = "insert into user_info "
. "(name) values "
. "('owen'), ('john'), ('lily')";
if (!$result = $db->query($sql))
echo "insert failed, error: ", $db->error;
else
echo "last insert id in query is ", $db->insert_id, "\n";
$sql = "insert into user_info"
. "(name) values "
. "('jim');";
$sql .= "insert into house_info "
. "(address) values "
. "('shenyang')";
if (!$db->multi_query($sql))
echo "insert failed in multi_query, error: ", $db->error;
else {
echo "last insert id in first multi_query is ", $db->insert_id, "\n";
if ($db->more_results() && $db->next_result())
echo "last insert id in second multi_query is ", $db->insert_id, "\n";
else
echo "insert failed in multi_query, second query error is ", $db->error;
}
$db->close();
}
?>
You will get output like this:
last insert id in query is 1
last insert id in first multi_query is 4
last insert id in second multi_query is 1
Conclusion:
1 insert_id works in multi_query
2 insert_id is the first id mysql has used if you have insert multi values
[#3] bert at nospam thinc dot nl [2008-07-22 08:58:08]
Watch out for the oo-style use of $db->insert_id. When the insert_id exceeds 2^31 (2147483648) fetching the insert id renders a wrong, too large number. You better use the procedural mysqli_insert_id( $db ) instead.
[EDIT by danbrown AT php DOT net: This is another prime example of the limits of 32-bit signed integers.]
[#4] Nick Baicoianu [2007-05-04 01:10:09]
When running extended inserts on a table with an AUTO_INCREMENT field, the value of mysqli_insert_id() will equal the value of the *first* row inserted, not the last, as you might expect.
<?php
//mytable has an auto_increment field
$db->query("INSERT INTO mytable (field1,field2,field3) VALUES ('val1','val2','val3'),
('val1','val2','val3'),
('val1','val2','val3')");
echo $db->insert_id; //will echo the id of the FIRST row inserted
?>
[#5] will at phpfever dot com [2006-04-20 18:40:16]
I have received many statements that the insert_id property has a bug because it "works sometimes". Keep in mind that when using the OOP approach, the actual instantiation of the mysqli class will hold the insert_id.
The following code will return nothing.
<?php
$mysqli = new mysqli('host','user','pass','db');
if ($result = $mysqli->query("INSERT INTO t (field) VALUES ('value');")) {
echo 'The ID is: '.$result->insert_id;
}
?>
This is because the insert_id property doesn't belong to the result, but rather the actual mysqli class. This would work:
<?php
$mysqli = new mysqli('host','user','pass','db');
if ($result = $mysqli->query("INSERT INTO t (field) VALUES ('value');")) {
echo 'The ID is: '.$mysqli->insert_id;
}
?>
[#6] alan at commondream dot net [2004-11-03 11:44:00]
I was having problems with getting the inserted id, and did a bit of testing. It ended up that if you commit a transaction before getting the last inserted id, it returns 0 every time, but if you get the last inserted id before committing the transaction, you get the correct value.