等价于mySQL中的SQLServer函数SCOPE_IDENTITY()? [英] The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?

问题描述

mySQL中等效于SQLServer函数 SCOPE_IDENTITY() ?

What is the equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?

推荐答案

这是您要寻找的:

LAST_INSERT_ID()

为回应OP的评论，我创建了以下基准测试:

In response to the OP's comment, I created the following bench test:

CREATE TABLE Foo
(
    FooId INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE Bar
(
    BarId INT AUTO_INCREMENT PRIMARY KEY
);

INSERT INTO Bar () VALUES ();
INSERT INTO Bar () VALUES ();
INSERT INTO Bar () VALUES ();
INSERT INTO Bar () VALUES ();
INSERT INTO Bar () VALUES ();

CREATE TRIGGER FooTrigger AFTER INSERT ON Foo
    FOR EACH ROW BEGIN
        INSERT INTO Bar () VALUES ();
    END;

INSERT INTO Foo () VALUES (); SELECT LAST_INSERT_ID();

这将返回:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

因此它使用原始表的LAST_INSERT_ID()而不是触发器内部的INSERT表.

So it uses the LAST_INSERT_ID() of the original table and not the table INSERTed into inside the trigger.

编辑:尽管最后的结论是正确的，但我一直都在意识到答案中显示的SELECT LAST_INSERT_ID()的结果是错误的.我已将结果更新为正确的值.

I realized after all this time that the result of the SELECT LAST_INSERT_ID() shown in my answer was wrong, although the conclusion at the end was correct. I've updated the result to be the correct value.

这篇关于等价于mySQL中的SQLServer函数SCOPE_IDENTITY()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！