使用PDO获取最后插入的行ID(不合适的结果) [英] getting last inserted row id with PDO (not suitable result)

问题描述

我遇到了PDO::lastInsertId()方法的问题，该方法没有返回最后插入的行的id(主键)，而是返回了另一个字段，即外键字段.

I have a problem with PDO::lastInsertId() method which doesn't return the id (primary key) of last inserted row, instead it returns another field which is a foreign key field.

PHP代码:

PHP code:

$pdo = new PDO(...);
$stmt = $pdo->prepare($sql);
$stmt->bindParam(...);
$stmt->bindParam(...);
$stmt->execute();
$id = $pdo->lastInsertId();
// or
$id = $pdo->lastInsertId('services_id_seq'); // I think 'services_id_seq' is not necessary in MySQL
// both of them don't return the primary key of last inserted row

echo 'last inserted id: ' . $id;

MySQL表结构:

MySQL Table structure:

...
id          int unsigned not null primary key auto_increment
customer_id int unsigned not null
user_id     int unsigned not null
....

在MySQL中插入行:

inserted row in MySQL:

id    customer_id    user_id    ...
1     19             31         ...

PHP输出:

PHP output:

last inserted id: 19

它应该返回1而不是19.我不知道我的代码有什么问题..也许这是正常现象:?

It should return 1 not 19. I don't know is anything wrong with my code or not.. or maybe this is the normal behavior :?

返回值(PHP文档):

• 如果未为name参数指定序列名称， PDO::lastInsertId()返回代表以下行的ID的字符串 插入数据库的最后一行.

• If a sequence name was not specified for the name parameter, PDO::lastInsertId() returns a string representing the row ID of the last row that was inserted into the database.

如果为name参数指定了序列名称，则PDO::lastInsertId()返回一个字符串，该字符串表示从指定序列对象中检索到的最后一个值.

If a sequence name was specified for the name parameter, PDO::lastInsertId() returns a string representing the last value retrieved from the specified sequence object.

如果PDO驱动程序不支持此功能，则PDO::lastInsertId()会触发IM001 SQLSTATE.

If the PDO driver does not support this capability, PDO::lastInsertId() triggers an IM001 SQLSTATE.

推荐答案

运行"SELECT LAST_INSERT_ID()"查询.
如果返回的ID仍为19，而不是2(或在尝试后应使用任何主键)，则MySQL存在问题，与PDO无关.您必须将它作为一个单独的案例进行研究(可能还需要一个单独的问题)，提供完整的SQL证明代码，能够在控制台中运行，包括创建表，运行插入并选择LAST_INSERT_ID()

Run "SELECT LAST_INSERT_ID()" query.
If returned ID is still 19, not 2 (or whatever primary key should be after all tries), there is a problem with MySQL, irrelevant to PDO. You have to investigate it as a separate case (and, probably, separate question), supplying full SQL proof-code, able to run in console, involving creating a table, running insert and selecting LAST_INSERT_ID()

如果此函数返回正确的值，但PDO仍然是错误的-您可能必须在bugs.php.net上对其进行错误报告，并再次提供完整的可复制代码以及所有提供正确版本号的软件名称.

If this function returns the right value but PDO still wrong one - you have to probably bugreport it on bugs.php.net, again with full reproduceable code and all software names with exact version numbers provided.

只有一件事要明确:您确定问题中的$sql变量包含正确的INSERT语句，而不是诸如INSERT ON DUPLICATE之类的东西吗? 还是在此INSERT上设置了任何触发器?

Only one thing to make it clear: are you certainly sure that $sql variable in your question contains proper INSERT statement, not something like INSERT ON DUPLICATE or such? Or are there any triggers set on this INSERT?

这篇关于使用PDO获取最后插入的行ID(不合适的结果)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！