PHP MySQL 获取最后插入的 ID
获取最后插入的记录的 ID
如果我们在有 AUTO_INCREMENT
字段的表上执行 INSERT
或 UPDATE
操作,我们可以立即获取最后插入/更新的记录的 ID。
在 "MyGuests" 表中,id
列是一个 AUTO_INCREMENT
字段:
CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
下例与上一页中的例子相同,除了我们添加了一行代码来检索最后插入的记录的 ID。我们还输出了最后插入的 ID:
实例(MySQLi 面向对象)
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // 创建连接 $conn = new mysqli($servername, $username, $password, $dbname); // 检查连接 if ($conn->connect_error) { die("连接失败:" . $conn->connect_error); } $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"; if ($conn->query($sql) === TRUE) { $last_id = $conn->insert_id; echo "新记录已成功创建。最后插入的ID是:" . $last_id; } else { echo "错误:" . $sql . "<br>" . $conn->error; } $conn->close(); ?>
实例(MySQLi 过程化)
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // 创建连接 $conn = mysqli_connect($servername, $username, $password, $dbname); // 检查连接 if (!$conn) { die("连接失败:" . mysqli_connect_error()); } $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"; if (mysqli_query($conn, $sql)) { $last_id = mysqli_insert_id($conn); echo "新记录已成功创建。最后插入的ID是:" . $last_id; } else { echo "错误:" . $sql . "<br>" . mysqli_error($conn); } mysqli_close($conn); ?>
实例(PDO)
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // 将 PDO 错误模式设置为异常 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"; // 使用exec(),因为没有返回结果 $conn->exec($sql); $last_id = $conn->lastInsertId(); echo "新记录已成功创建。最后插入的 ID 是:" . $last_id; } catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage(); } $conn = null; ?>