id = LAST_INSERT_ID（id）的含义

Supposing I have a table:

假设我有一张桌子:

CREATE TABLE files (
    id_prod INT UNSIGNED NOT NULL DEFAULT PRIMARY KEY AUTO_INCREMENT,
    id_rel INT UNSIGNED,
    name VARCHAR(250),
    other VARCHAR(200),
    UNIQUE INDEX(id_rel , name)
);

and I want to use an unique query to insert/update the data on this table:

我想使用一个唯一的查询来插入/更新此表上的数据:

INSERT INTO files (id_rel , name)
VALUES ('25', 'test')
ON DUPLICATE KEY UPDATE

now, reading the MySQL manual I read about this:

现在,阅读我读到的MySQL手册:

ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)

so I thought my query should be:

所以我认为我的查询应该是:

INSERT INTO files (id_rel , name)
VALUES ('25', 'test')
ON DUPLICATE KEY UPDATE id_prod = LAST_INSERT_ID(id), name = 'TESTED'

but which is the difference if I use only:

但如果我只使用以下是不同的:

INSERT INTO files (id_rel , name)
VALUES ('25', 'test')
ON DUPLICATE KEY UPDATE name = 'TESTED'

I cannot understand the meaning of LAST_INSERT_ID(id). What is (id) and what it's supposed to do?

我无法理解LAST_INSERT_ID(id)的含义。什么是(id)以及它应该做什么?

1 个解决方案

#1

This is only necessary if your application needs to call LAST_INSERT_ID() after performing the INSERT. Normally, LAST_INSERT_ID() will only return a value if you actually inserted a new row into the table, not of there was a duplicate key and it updated the row instead.

只有在执行INSERT后应用程序需要调用LAST_INSERT_ID()时,才需要这样做。通常,如果您实际在表中插入了新行,LAST_INSERT_ID()将仅返回一个值,而不是重复键,而是更新了行。

From the documentation:

从文档:

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().

如果将expr作为LAST_INSERT_ID()的参数给出,则该函数返回该参数的值,并将其记为LAST_INSERT_ID()返回的下一个值。

If you use the idiom you quoted, LAST_INSERT_ID() will return either the ID of the new row that was inserted or the row that was updated.

如果您使用引用的惯用语,LAST_INSERT_ID()将返回插入的新行的ID或更新的行。

#1