39

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

kristof
  • 52,923
  • 24
  • 87
  • 110

3 Answers3

78

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.

Sean Bright
  • 118,630
  • 17
  • 138
  • 146
  • Thanks Sean. How does it behave when the table I am inserting the data into has a trigger that inserts data in another table which has an autoincrement field too? Would it return the ID of the original table or the one affected by the trigger? – kristof Feb 18 '09 at 12:16
3

open MySql command type SELECT LAST_INSERT_ID(); then ENTER

Ramgy Borja
  • 2,330
  • 2
  • 19
  • 40
1
CREATE TABLE Identity_Temp
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Name varchar(200)
);

INSERT INTO Identity_Temp (Name) VALUES ('A');
INSERT INTO Identity_Temp (Name) VALUES ('A');

SELECT LAST_INSERT_ID();

DROP TABLE Identity_Temp;

You get last inserted identity value from using LAST_INSERT_ID() and you can used any table insert as well

toyota Supra
  • 3,181
  • 4
  • 15
  • 19
Dipak Pipavat
  • 11
  • 2