使用last_insert_id()插入重复的密钥更新 [英] INSERT ON DUPLICATE KEY UPDATE with last_insert_id()

问题描述

我正在尝试创建一个函数

im trying to create a function

CREATE FUNCTION `func`(param1 INT, param2 INT, param3 TEXT) RETURNS int(11)
BEGIN
INSERT INTO `table1` (`column1`, `column2`, `column3` ) 
VALUES (param1, param2, param3)
ON DUPLICATE KEY 
UPDATE `time_stamp` = UNIX_TIMESTAMP();
RETURN last_insert_id();
END

这将在表中插入一行(如果不存在的话)，否则将对其进行更新. 请注意，我返回了last_insert_id()，如果该函数将插入将是正确的，否则它将在更新时无法预测.

this would insert into a table a row if it doesn't exist but otherwise update it. Notice that i returned last_insert_id() which would be correct if the function would insert otherwise would be unpredictable if it updates.

我知道解决此问题的替代方法是使用单独的SELECTS并确定其是否存在；如果存在，则使用该id检索id和update；否则，只需执行简单的INSERT.

I know the alternative to solving this is using separate SELECTS and identify if it exists; if it exists retrieve the id and update using that id; otherwise just do a plain INSERT.

现在我的问题是:除了我现在正在做的2个sql语句之外，还有其他选择吗?

Now my question: Is there any alternative to doing 2 sql statements as opposed to what i'm doing now?

编辑1

附录:

有一个自动递增的索引. 所有要插入的值都是唯一的

there is an auto incremented index. All of the values to be inserted are unique

我宁愿不更改索引，因为它是在另一个表中引用的.

I'd rather not alter the index since it is being referred in another table..

推荐答案

如果表包含AUTO_INCREMENT列并且INSERT ... UPDATE插入行，则LAST_INSERT_ID()函数将返回AUTO_INCREMENT值.如果该语句更新一行，则LAST_INSERT_ID()是没意义的.但是，您可以使用LAST_INSERT_ID(expr) 解决方法.假设id是AUTO_INCREMENT列.为了使LAST_INSERT_ID()对更新有意义，请按如下所示插入行:

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a, b, c) VALUES (1, 2, 3)
  ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), c = 3;

在此链接上找到了它.虽然我从未尝试过，但是它可能会对您有所帮助.

Found it on this link. I've never tried it though, but it might help you.

编辑1

您可能想查看替换:

You might want to check out REPLACE:

REPLACE INTO table1 (column1, column2, column3) VALUES (param1, param2, param3);

这应该适用于具有正确PRIMARY KEY/UNIQUE INDEX的表.

This should work for tables with correct PRIMARY KEY/UNIQUE INDEX.

最后，您只需要坚持:

IF (VALUES EXISTS ON TABLE ...)
    UPDATE ...
    SELECT Id;
ELSE
    INSERT ...
    RETURN last_insert_id();
END IF

这篇关于使用last_insert_id()插入重复的密钥更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！