The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?

by

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.

More Related Contents:

  1. SQL Query needed for fetch value from Two identical table but not fully identical table
  2. sql query to modify the selected value from database [closed]
  3. SQL Print the name of all Employees together with the name of their supervisor
  4. Does SQL Server Offer Anything Like MySQL’s ON DUPLICATE KEY UPDATE
  5. SQL: Repeat a result row multiple times, and number the rows
  6. How to export SQL Server database to MySQL? [duplicate]
  7. How to transform a MSSQL CTE query to MySQL?
  8. Is there a Profiler equivalent for MySql? [closed]
  9. Why do results from a SQL query not come back in the order I expect?
  10. When to use SELECT … FOR UPDATE?
  11. Best practices for SQL varchar column length [closed]
  12. Relationship between catalog, schema, user, and database instance
  13. Import MySQL database into a SQL Server
  14. How to export data from SQL Server 2005 to MySQL [closed]
  15. MySQL Aggregate Functions without GROUP BY clause
  16. mysql equivalent data types
  17. What resources exist for Database performance-tuning? [closed]
  18. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  19. How to migrate SQL Server database to MySQL? [closed]
  20. how to cast the hexadecimal to varchar(datetime)?
  21. How to write a (MySQL) “LIMIT” in SQL Server?
  22. sql group by versus distinct
  23. How to import a SQL Server .bak file into MySQL?
  24. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  25. Equivalent of MSSQL IDENTITY Column in MySQL
  26. Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server
  27. What are the max number of allowable parameters per database provider type?
  28. Why in SQL NULL can’t match with NULL?
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment