mysqli_insert_id()函数返回最后一条插入语句产生的自增 ID
如果您有一个具有列的AUTO_INCREMENT属性的表,并且如果您的最后一次MySQLi函数调用执行INSERT或UPDATE语句。 函数的作用是:返回自动生成的上次执行查询的ID。
mysqli_insert_id($con)
序号 | 参数及说明 |
---|---|
1 | con(必需) 这是一个表示与MySQL Server的连接的对象。 |
mysqli_insert_id() 函数返回最后一个 SQL 语句(通常是 INSERT 语句) 所操作的表中设置为 AUTO_INCREMENT 的列的值。 如果最后一个 SQL 语句不是 INSERT 或者 UPDATE 语句, 或者所操作的表中没有设置为 AUTO_INCREMENT 的列, 返回值为 0。
此函数最初是在PHP版本5中引入的,并且可以在所有更高版本中使用。
假设我们在数据库mydb中创建了一个名为Cricketers的表,其中字段ID为PRIMARY KEY,并且为AUTO INCREMENTED:
CREATE TABLE Cricketers( ID INT PRIMARY KEY AUTO_INCREMENT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) );
以下示例演示了mysqli_insert_id()函数的用法(面向过程风格)-
<?php //建立连接 $con = mysqli_connect("localhost", "root", "password", "mydb"); //在employee表中插入记录 $sql = "insert into Cricketers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India')"; mysqli_query($con, $sql); //插入ID $id = mysqli_insert_id($con); print("Insert ID: ".$id ."\n"); $sql = "insert into Cricketers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica')"; mysqli_query($con, $sql); $id = mysqli_insert_id($con); print("Insert ID: ".$id); //关闭连接 mysqli_close($con); ?>
输出结果
Insert ID: 1 Insert ID: 2
在面向对象的样式中,此函数的语法为$ con-> insert_id();。以下是面向对象样式$ minus的This函数的示例;
<?php //建立连接 $con = new mysqli("localhost", "root", "password", "mydb"); //在employee表中插入记录 $con -> query("insert into Cricketers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka')"); //Insert ID $state = $con->insert_id; print("Insert ID: ".$state."\n"); //在employee表中插入记录 $con -> query("insert into Cricketers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India')"); //Insert ID $state = $con->insert_id; print("Insert ID: ".$state); //关闭连接 $con -> close(); ?>
输出结果
Insert ID: 3 Insert ID: 4
以下是函数mysqli_insert_id的另一个示例-
<?php //建立连接 $con = mysqli_connect("localhost", "root", "password", "mydb"); //选择Cricketers表的所有行的查询 mysqli_query($con, "SELECT * FROM Cricketers"); print("Insert ID (select query): ".mysqli_insert_id($con)."\n"); //用于将多行插入到Cricketers表中的查询 mysqli_query($con, "INSERT INTO Cricketers VALUES (5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'), (6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India') "); print("Insert ID: (multiple inserts) ".mysqli_insert_id($con)."\n"); //查询以更新employee表的行 mysqli_query($con, "UPDATE Cricketers set COUNTRY = 'S.Africa' where ID = 2"); print("Insert ID (update query): ".mysqli_insert_id($con)."\n"); //查询将记录插入到employee表中 mysqli_query($con, "INSERT INTO employee VALUES ('Sarmista', 'Sharma', 28, 'F', 15000, 101)"); print("Insert ID: (table with out auto incremented key) ".mysqli_insert_id($con)."\n"); //关闭连接 mysqli_close($con); ?>
输出结果
Insert ID (select query): 0 Insert ID: (multiple inserts) 6 Insert ID (update query): 0 Insert ID: (table with out auto incremented key) 0
以下示例演示了SELECT,UPDATE,INSERT和DELETE查询中mysqli_insert_id函数的用法-
<?php $connection_mysql = mysqli_connect("localhost", "root", "password", "mydb"); if (mysqli_connect_errno($connection_mysql)){ echo "连接MySql失败: " . mysqli_connect_error(); } mysqli_query($connection_mysql,"INSERT INTO Employee (name) VALUES('PHP')"); echo "New record has id: " . mysqli_insert_id($connection_mysql); mysqli_close($connection_mysql); ?>
输出结果
New record has id: 0