使用INSERT ON DUPLICATE KEY UPDATE和last_insert_id()

Question

使用INSERT ON DUPLICATE KEY UPDATE和last_insert_id()

3

我正在尝试创建一个函数

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()，如果该函数插入，它将是正确的，否则如果它更新将是不可预测的。

我知道解决这个问题的替代方法是使用单独的SELECTS，并确定是否存在；如果存在，则检索id并使用该id进行update；否则只需执行普通的INSERT。

现在我的问题是：有没有其他方法可以执行2个sql语句，而不是现在正在做的事情？

编辑1：

附加说明：

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

我不想改变索引，因为它被另一个表中引用。

- ianace

请提供 SHOW CREATE TABLE。哪一个是唯一的，列1、列2还是列3？ - Devart

可能是MySQL ON DUPLICATE KEY - last insert id?的重复问题。 - Steve Chambers

2个回答

0

如果有人从谷歌来到这里，我遇到了一个问题，ON DUPLICATE KEY UPDATE 一直触发相同的错误值。

当只插入用户的名字和姓氏时，它没有 AUTO_INCREMENT 主键。原因是我们有一个带有用户名唯一约束的 users 表，但它的默认值为 ''。因此，当您插入一个没有用户名的用户时，它会触发更新该用户名的重复值，并且那个随机账户一直被返回为正确的账户。

解决方案是确保在具有单独的自动增量主键的表中，唯一键的默认值仅为 NULL，或者您生成唯一约束的唯一值。

- Michael Ryan Soileau

这在被接受的答案的第一条评论中指出了。 - ianace

不，不是这样的。我遇到的问题是默认值被设置为字符串而不是null。这导致了对主键的更新，即使不应该更新。第一个评论是：“唯一需要考虑的事情是，在依赖于非增量列的唯一列时，你的id递增可能会出现异常。然而，当你达到那个点时，有其他可靠的解决方法。”这显然不是一个解释它如何会导致问题的方法。 - Michael Ryan Soileau

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- KaeL · Accepted Answer

如果一个表包含一个AUTO_INCREMENT列，并且INSERT ... UPDATE插入一行，则LAST_INSERT_ID()函数返回AUTO_INCREMENT值。如果语句更新一行，则LAST_INSERT_ID()无意义。但是，您可以通过使用LAST_INSERT_ID(expr)来解决这个问题。假设id是AUTO_INCREMENT列。为了使更新时LAST_INSERT_ID()有意义，请按以下方式插入行：

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

我在这个链接上找到了它。虽然我从未尝试过，但它可能会对你有所帮助。 编辑1 你可能想要查看REPLACE:

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

这适用于具有正确的PRIMARY KEY/UNIQUE INDEX的表格。

最终，您只需要坚持以下做法：

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