php - 当表先前被锁定时是否可以使用 PDO lastInsertId() ？

Question

这个问题对我来说又很重要。有没有人有解决办法？

$conn = new PDO('mysql:dbname=test;host=127.0.0.1', 'root', '********');

$conn->exec('CREATE TABLE testIncrement ' .
            '(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50))');
$sth = $conn->prepare('INSERT INTO testIncrement (name) VALUES (:name);');
$sth->execute([':name' => 'foo']);
var_dump($conn->lastInsertId());

输出为:string(1)“lastInsertId”。但是当我锁定表时，lastInsertId 始终为 0。所以这段代码总是返回0:

$conn = new PDO('mysql:dbname=test;host=127.0.0.1', 'root', 'paragraf');

$conn->exec('CREATE TABLE testIncrement ' .
            '(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50))');
$sth = $conn->prepare('LOCK TABLE testIncrement WRITE; INSERT INTO testIncrement (name) VALUES (:name); UNLOCK TABLES;');
$sth->execute([':name' => 'foo']);
var_dump($conn->lastInsertId());

结论:表锁定时是否可能以及如何获取lastInsertId？还是我哪里写错了？

@埃内斯塔斯我尝试了你的建议，结果如下:(

代码:

$sthLastId = $conn->prepare('SELECT LAST_INSERT_ID();');
$sthLastId->execute();
print_r($sthLastId->fetchAll());

//Output when there is no lock: **string(2) "40" Array ( [0] => Array ( [LAST_INSERT_ID()] => 40 [0] => 40 ) )**
//And output when lock is use: **string(1) "0" Array ( )** 

MySQL版本:5.6.26

Answer 1

一切看起来都很好。尝试在解锁后添加SELECT LAST_INSERT_ID()。我不知道为什么 PDO 不适合你。

MySQL版本:5.5.44

寻找不同的答案:MySQL and PDO: Could PDO::lastInsertId theoretically fail?