在 PHP 中使用 ON DUPLICATE KEY UPDATE 时获取 mysql_insert_id()

发布于 2024-08-28 23:34:10 字数 672 浏览 10 评论 0原文

我单独使用 mySQL 找到了一些答案,但我希望有人可以向我展示一种在使用 PHP 处理插入/更新时获取 mysql 数据库最后插入或更新行的 ID 的方法。

目前我有这样的东西,其中column3是唯一键,还有一个id列是自动增量主键:

$query ="INSERT INTO TABLE (column1, column2, column3) VALUES (value1, value2, value3) ON DUPLICATE KEY UPDATE SET column1=value1, column2=value2, column3=value3";
mysql_query($query);

$my_id = mysql_insert_id();

$my_id在INSERT上是正确的,但在更新行时不正确(ON DUPLICATE KEY UPDATE)。

我看过几篇帖子,有人建议您

INSERT INTO table (a) VALUES (0) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) 

在调用 ON DUPLICATE KEY 时使用类似的方法来获取有效的 ID 值 - 但这会将该有效 ID 返回到 PHP mysql_insert_id() 函数吗?

I've found a few answers for this using mySQL alone, but I was hoping someone could show me a way to get the ID of the last inserted or updated row of a mysql DB when using PHP to handle the inserts/updates.

Currently I have something like this, where column3 is a unique key, and there's also an id column that's an autoincremented primary key:

$query ="INSERT INTO TABLE (column1, column2, column3) VALUES (value1, value2, value3) ON DUPLICATE KEY UPDATE SET column1=value1, column2=value2, column3=value3";
mysql_query($query);

$my_id = mysql_insert_id();

$my_id is correct on INSERT, but incorrect when it's updating a row (ON DUPLICATE KEY UPDATE).

I have seen several posts with people advising that you use something like

INSERT INTO table (a) VALUES (0) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) 

to get a valid ID value when the ON DUPLICATE KEY is invoked-- but will this return that valid ID to the PHP mysql_insert_id() function?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

荒路情人 2024-09-04 23:34:10

这是 Alexandre 建议的答案:

当您使用 id=LAST_INSERT_ID(id) 时,它会设置 mysql_insert_id = 更新的 ID 的值 - 所以您的最终代码应该如下所示:

<?
    $query = mysql_query("
        INSERT INTO table (column1, column2, column3) 
        VALUES (value1, value2, value3) 
        ON DUPLICATE KEY UPDATE
            column1 = value1, 
            column2 = value2, 
            column3 = value3, 
            id=LAST_INSERT_ID(id)
    ");
    $my_id = mysql_insert_id();

这将返回 $my_id 的正确值,无论更新或插入。

Here's the answer, as suggested by Alexandre:

when you use the id=LAST_INSERT_ID(id) it sets the value of mysql_insert_id = the updated ID-- so your final code should look like:

<?
    $query = mysql_query("
        INSERT INTO table (column1, column2, column3) 
        VALUES (value1, value2, value3) 
        ON DUPLICATE KEY UPDATE
            column1 = value1, 
            column2 = value2, 
            column3 = value3, 
            id=LAST_INSERT_ID(id)
    ");
    $my_id = mysql_insert_id();

This will return the right value for $my_id regardless of update or insert.

孤君无依 2024-09-04 23:34:10

您可以检查查询是插入还是更新( mysql_affected_rows(); 在插入时返回 1,在更新时返回 2)。

如果是插入,则使用 mysql_insert_id,如果是更新,则需要另一个查询。

<?php
$query ="INSERT INTO TABLE (column1, column2, column3) VALUES (value1, value2, value3) ON DUPLICATE KEY UPDATE SET column1=value1, column2=value2, column3=value3";
mysql_query($query);
if(mysql_affected_rows() == 1) { $id = mysql_insert_id(); }
else { // select ... 
}
?>

我知道这并不完全是您想要的,但这是我能想到的最好的

You could check if the Query was an insert or an update ( mysql_affected_rows(); returns 1 on insert and 2 on update).

If it was an insert use mysql_insert_id, if it was an update you'd need another Query.

<?php
$query ="INSERT INTO TABLE (column1, column2, column3) VALUES (value1, value2, value3) ON DUPLICATE KEY UPDATE SET column1=value1, column2=value2, column3=value3";
mysql_query($query);
if(mysql_affected_rows() == 1) { $id = mysql_insert_id(); }
else { // select ... 
}
?>

I know it's not excatly what your looking for but it's the best i could come up with

沩ん囻菔务 2024-09-04 23:34:10

尽管不使用 mysql_insert_id() 和 ON DUPLICATE KEY UPDATE,但在更新另一个字段时获取任何字段的值的另一种好方法找到了 这里

UPDATE table SET id=(@tempid:=id) , .... LIMIT 1;
SELECT @tempid;

我使用它的表带有(id,status)'id'主索引自动增量,'status'是进行更新的字段,但我需要获取更新行的“id”。该解决方案还证明了 mysql_insert_id() 的竞争条件。

Although not using mysql_insert_id() and ON DUPLICATE KEY UPDATE, alternative great way to get the value of any field when updating another found here:

UPDATE table SET id=(@tempid:=id) , .... LIMIT 1;
SELECT @tempid;

I used it having table with (id,status) 'id' primary index auto-increment, and 'status' was the field upon which update was made, but i needed to get 'id' of the updated row. This solution also proof to race conditions as mysql_insert_id().

挥剑断情 2024-09-04 23:34:10

这是我的解决方案,您将数据放入单个数组中,它会自动复制/填充到“INSERT INTO .. ON DUPLICATE UPDATE ..”查询中。

它对于可维护性非常有用,如果您愿意,您也可以将其设为函数/方法。

// save to db:

$qData = [
    "id" =>                mysql_real_escape_string($email_id),     
    "synd_id" =>           mysql_real_escape_string($synd_id),
    "campaign_id" =>       mysql_real_escape_string($campaign_id),
    "event_id" =>          mysql_real_escape_string($event_id),
    "user_id" =>           mysql_real_escape_string($user_id),
    "campaign_name" =>     mysql_real_escape_string($campaign_name), 
    "subject" =>           mysql_real_escape_string($subject),
    "from_name"=>          mysql_real_escape_string($from_name),
    "from_email"=>         mysql_real_escape_string($from),
    "content"=>            mysql_real_escape_string($html),
    "link_to_template" =>  mysql_real_escape_string($hash),
    "ext_campaign_id" =>   mysql_real_escape_string($ext_campaign_id),
    "ext_list_id"=>        mysql_real_escape_string($ext_list_id),
];


$q = "INSERT INTO email_campaigns (".
  // i.e create a string like `id`, `synd_id`, `campaign_id`..  with linebreaks for readability
  implode(", \n", array_map(function($k){ return "`$k`"; }, array_keys($qData)))
.")
VALUES (".
  // i.e '20', '532', '600' .. 
  implode(", \n", array_map(function($v){ return "'$v'"; }, array_values($qData)))
." )  ON DUPLICATE KEY UPDATE ".
  // i.e `synd_id`='532', `campaign_id`='600' ... 
  // id & link_to_template table keys are excluded based on the array below
  implode(", \n", array_filter(array_map(function($k, $v){ if(!in_array($k, ['id', 'link_to_template']) ) return "`$k`='$v'" ; }, array_keys($qData), array_values($qData))))  ;

This is my solution where you put the data into a single array and it's automatically duplicated/populated into the "INSERT INTO .. ON DUPLICATE UPDATE .. " query.

It's great for maintainability, and if you want you can make it a function / method too.

// save to db:

$qData = [
    "id" =>                mysql_real_escape_string($email_id),     
    "synd_id" =>           mysql_real_escape_string($synd_id),
    "campaign_id" =>       mysql_real_escape_string($campaign_id),
    "event_id" =>          mysql_real_escape_string($event_id),
    "user_id" =>           mysql_real_escape_string($user_id),
    "campaign_name" =>     mysql_real_escape_string($campaign_name), 
    "subject" =>           mysql_real_escape_string($subject),
    "from_name"=>          mysql_real_escape_string($from_name),
    "from_email"=>         mysql_real_escape_string($from),
    "content"=>            mysql_real_escape_string($html),
    "link_to_template" =>  mysql_real_escape_string($hash),
    "ext_campaign_id" =>   mysql_real_escape_string($ext_campaign_id),
    "ext_list_id"=>        mysql_real_escape_string($ext_list_id),
];


$q = "INSERT INTO email_campaigns (".
  // i.e create a string like `id`, `synd_id`, `campaign_id`..  with linebreaks for readability
  implode(", \n", array_map(function($k){ return "`$k`"; }, array_keys($qData)))
.")
VALUES (".
  // i.e '20', '532', '600' .. 
  implode(", \n", array_map(function($v){ return "'$v'"; }, array_values($qData)))
." )  ON DUPLICATE KEY UPDATE ".
  // i.e `synd_id`='532', `campaign_id`='600' ... 
  // id & link_to_template table keys are excluded based on the array below
  implode(", \n", array_filter(array_map(function($k, $v){ if(!in_array($k, ['id', 'link_to_template']) ) return "`$k`='$v'" ; }, array_keys($qData), array_values($qData))))  ;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文