2014-11-20 08:35
浏览 199


I have the following code:

$add_rq = $DBH->prepare('INSERT INTO `table` SET `url` = :url ON DUPLICATE KEY UPDATE `url`=`url`');
if($add_rq->execute(['url' => $url]))
  echo "Added (id: ".$DBH->lastInsertId().")";
  echo "Not added";

If the inserting url already exist in the table the ON DUPLICATE KEY UPDATE statement working but the return value of the execute() is always TRUE. However lastInsertId() is zero in this case. I COULD use this as the indication of the insertion duplication or I could do one more query to DB prior to the insert but both approaches looks bad to me.

Is there any better way?

图片转代码服务由CSDN问答提供 功能建议


  $ add_rq = $  DBH->准备('INSERT INTO`table`SET`URL` =:url ON DUPLICATE KEY UPDATE`url` =`url`'); 
if($ add_rq-> execute(['url'=>  $ url]))
 echo“已添加(id:”。$ DBH-> lastInsertId()。“)”; 

如果 ON DUPLICATE KEY UPDATE 语句中的 url 已经存在,那么 execute()的返回值始终是 TRUE 。 但是,在这种情况下, lastInsertId()为零。 我可以使用它作为插入重复的指示,或者我可以在插入之前再对DB进行一次查询,但这两种方法对我来说都很糟糕。

有没有更好的方法?< / p>

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongting7352 2014-11-20 08:41

    You can use $add_rq->rowCount(). According to a comment in the documentation, it will return 1 if a new row was inserted, 2 if an existing row was updated. But apparently that's wrong, it returns 0 if a row was updated.

    execute() only returns false if it fails because of an error.

    解决 无用
    打赏 举报
  • dptpn06684 2014-11-20 09:12

    Update the id if it is duplicate

    Then lastInsertId() will return the id (instead of 0) after an update
    It will return the id after an insert either way

    INSERT INTO `table` SET `url` = :url ON DUPLICATE KEY UPDATE `url`=`url`, id=LAST_INSERT_ID(id)

    now this will return the id even if no new rows were inserted


    To find out if there was an insert of update, you could add another column called "updated" with a default value of 0, then when it updates it should set the column to 1

    INSERT INTO `table` (`url`) VALUES (?) ON DUPLICATE KEY UPDATE `url`=?, `updated`=`updated`+1

    Source: it's mentioned at the bottom of the mysql manual
    MySQL ON DUPLICATE KEY - last insert id?

    解决 无用
    打赏 举报

相关推荐 更多相似问题