問題描述

所有查詢都成功執行，當我檢查 MySQL 行中的表插入成功且沒有任何錯誤，但 lastInsertId() 返回 0.為什么?

All queries execute successfully, when I check table in MySQL row inserted successfully without any error, but lastInsertId() returns 0. why?

我的代碼:

// queries executes successfully, but lastInsetId() returns 0
// the menus table has `id` column with primary auto_increment index
// why lastInsertId return 0 and doesn't return actual id?


$insertMenuQuery = " 
 SELECT @rght:=`rght`+2,@lft:=`rght`+1 FROM `menus` ORDER BY `rght` DESC limit 1; 
 INSERT INTO `menus`(`parent_id`, `title`, `options`, `lang`, `lft`, `rght`) 
      values 
  (:parent_id, :title, :options, :lang, @lft, @rght);";
     try {
           // menu sql query
           $dbSmt = $db->prepare($insertMenuQuery);

           // execute sql query
           $dbSmt->execute($arrayOfParameterOfMenu);
           // menu id
           $menuId = $db->lastInsertId();

           // return
           return $menuId;

     } catch (Exception $e) {
          throw new ForbiddenException('Database error.' . $e->getMessage());
     }

推薦答案

我們必須使用 PDO_MySQL

With PDO_MySQL we must use

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE); // there are other ways to set attributes. this is one

以便我們可以運行多個查詢，例如:

so that we can run multiple queries like:

$foo = $DB->prepare("SELECT * FROM var_lst;INSERT INTO var_lst (value) VALUES ('durjdn')");

但遺憾的是，這樣做可以減輕 $DB 返回正確插入 ID 的負擔.您必須單獨運行它們才能檢索插入 ID.這將返回正確的插入 ID:

but sadly, doing so relieves the $DB from returning the correct insert id. You would have to run them separately to be able to retrieve the insert id. This returns the correct insert id:

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE);
$foo = $DB->prepare("INSERT INTO var_lst (value) VALUES ('durjdn')");
$foo->execute();
echo $DB->lastInsertId();

但這不會:

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE);
$foo = $DB->prepare("SELECT * FROM var_lst;INSERT INTO var_lst (value) VALUES ('durjdn')");
$foo->execute();
echo $DB->lastInsertId();

這甚至不會運行兩個查詢:

and this won't even run the two queries:

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,FALSE); // When false, prepare() returns an error
$foo = $DB->prepare("SELECT * FROM var_lst;INSERT INTO var_lst (value) VALUES ('durjdn')");
$foo->execute();
echo $DB->lastInsertId();

這篇關于pdo lastInsertId 返回零(0)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！