id的含义= LAST_INSERT_ID(id) [英] Meaning of id = LAST_INSERT_ID(id)

问题描述

假设我有一张桌子:

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)
);

，我想使用唯一的query插入/更新此表上的数据:

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

现在，阅读有关此内容的MySQL手册:

now, reading the MySQL manual I read about this:

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'

?

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

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

推荐答案

仅当您的应用程序需要在执行INSERT之后调用LAST_INSERT_ID()时，才有必要.通常，LAST_INSERT_ID()仅在您实际上向表中插入了新行时才返回值，而不是有重复的键，而是更新了该行.

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.

从文档:

如果将 expr 用作LAST_INSERT_ID()的参数，则该参数的值由函数返回，并被记住为LAST_INSERT_ID()返回的下一个值.

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().

如果使用引用的惯用语，LAST_INSERT_ID()将返回插入的新行或更新的行的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.

这篇关于id的含义= LAST_INSERT_ID(id)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！