mysql identity sqlserver,等价于MySQL中的SQLServer函数SCOPE_IDENTITY（）？

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

解决方案

This is what you are looking for:

LAST_INSERT_ID()

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

This returns:

+------------------+

| LAST_INSERT_ID() |

+------------------+

| 1 |

+------------------+

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

Edit: 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.