MySQL INSERT with OUTPUT like MSSQL

Question

I am pretty sure MySQL does not have the INSERT INTO table OUTPUT [column value] that MSSQL does - http://msdn.microsoft.com/en-us/library/ms177564.aspx (or http://blogs.msdn.com/b/sqltips/archive/2005/06/13/output-clause.aspx)

What's an easy way to replicate this? (I am moving a MSSQL app to MySQL. The 'OUTPUT' is a unique identifier and a int currently, so maybe I could just SELECT MAX (int) and add one, and generate a UID prior to insert?)

Thanks

Is there an AUTO_INCREMENT on the column? If yes, use LAST_INSERT_ID. If no, SELECT MAX will have to do, indeed... — Konerak
– Konerak, Commented Mar 29, 2011 at 7:56
Note that MS SQL also has a feature like LAST_INSERT_ID(): SET @logSqlId = SCOPE_IDENTITY(); You can use logSqlId as an output parameter or SELECT from it. Also for future readers, consider Postgres if moving from one of the big commercial systems. Its capabilities are much closer to the high-end database systems and in some cases it surpasses them. — Charles Burns
– Charles Burns, Commented Mar 14, 2016 at 16:48

Galz · Accepted Answer · 2011-03-29 07:56:53Z

11

If this value is an auto-increment field, you can run SELECT LAST_INSERT_ID(); after running the insert, and you'll get the last value inserted into this field. See: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

answered Mar 29, 2011 at 7:56

Galz

6,8525 gold badges35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AaronM Over a year ago

Brilliant, haven't used MySQL much so wasn't aware of that feature, should do the trick and I can generate a GUID/unique identifier easily enough

Mr Zorn Over a year ago

Sadly this doesn't help at all if doing batch inserts, especially you need to do further matching up after the fact and need more than the id - resulting in an additional select.

Loren Pechtel Over a year ago

And what if someone else inserts something between your insert and the select last insert? Do you have to wrap it in a transaction??

user149341 Over a year ago

@LorenPechtel No. LAST_INSERT_ID() is local to the connection. In essence, it returns the last ID that you inserted, even if other connections may have inserted rows in the meantime.

Collectives™ on Stack Overflow

MySQL INSERT with OUTPUT like MSSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related