mysqli::$insert_id()
(PHP 5, PHP 7)
返回最后一条插入语句产生的自增 ID
说明
面向对象风格
mixed $mysqli->insert_id;过程化风格
mysqli_insert_id(mysqli$link): mixed
mysqli_insert_id()函数返回最后一个 SQL 语句(通常是 INSERT 语句)所操作的表中设置为 AUTO_INCREMENT 的列的值。如果最后一个 SQL 语句不是 INSERT 或者 UPDATE 语句,或者所操作的表中没有设置为 AUTO_INCREMENT 的列,返回值为 0。
Note:如果在所执行的 INSERT 或者 UPDATE 语句中使用了数据库函数 LAST_INSERT_ID()。有可能会影响mysqli_insert_id()函数的返回值。
参数
- $link
仅以过程化样式:由mysqli_connect()或mysqli_init()返回的链接标识。
返回值
最后一条 SQL(INSERT 或者 UPDATE)所操作的表中设置为AUTO_INCREMENT属性的列的值。如果指定的连接上尚未执行 SQL 语句,或者最后一条 SQL 语句所操作的表中没有设为AUTO_INCREMENT的列,返回 0。
Note:如果返回值超出了 php 允许的最大整数值,mysqli_insert_id()函数会以字符串形式返回这个值。
范例
Example #1$mysqli->insert_id例程
面向对象风格
<?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.
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; } ?>
msqli_insert_id(); This seems to return that last id entered. BUT, if you have multiple users running the same code, depending on the server or processor I have seen it return the wrong id. Test Case: Two users added an item to their list. I have had a few times where the id was the id from the other user. This is very very rare and it only happens on my test server and not my main server. I am guessing it is because of multicores (maybe hyperthreading) or how the operating system handles multi-threads. It is rare, but it happens.
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.
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.]
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. <? //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 ?>
When using "INSERT ... ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`)", the AUTO_INCREMENT will increase in an InnoDB table, but not in a MyISAM table.
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
Hi mail at nikha dot org, I must say your way of getting the key is not correct. Consider a multi user situation where everyone is registering and you are returning their id. Let's say A, B and C are registering at the same time. Regards Himadri
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??);