在PHP上使用ON DUPLICATE KEY UPDATE时获取mysql_insert_id() [英] getting mysql_insert_id() while using ON DUPLICATE KEY UPDATE with PHP
问题描述
我已经找到了单独使用mySQL的一些答案,但是我希望有人可以向我展示一种使用PHP处理插入/更新时获取mysql数据库最后插入或更新的行的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.
目前,我有类似这样的内容,其中column3是唯一键,并且还有一个id列是自动递增的主键:
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在INSERT上是正确的,但是在更新行时(在重复键更新上)是错误的.
$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)
在调用ON DUPLICATE KEY时获取有效的ID值-但这会将该有效ID返回给PHP mysql_insert_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?
推荐答案
这是亚历山大(Alexandre)建议的答案:
Here's the answer, as suggested by Alexandre:
当您使用id = LAST_INSERT_ID(id)时,它会设置mysql_insert_id =更新后的ID的值-因此您的最终代码应类似于:
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();
这将为$ my_id返回正确的值,而不考虑更新或插入.
This will return the right value for $my_id regardless of update or insert.
这篇关于在PHP上使用ON DUPLICATE KEY UPDATE时获取mysql_insert_id()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!