Last inserted ids?

[killmenot]

Hi,

What is the way of getting last inserted ids after bulk insert?

The way I use now is:

const start = result[0].insertId
const end = result[0].insertId + result[0].affectedRows

const lastInsertedIds = return _.range(start, end).map(x => x + 1)

Do anyone have thoughts / improvements about it?

[sidorares]

Hi @killmenot

I don't think it's possible. You'll have to do extra query to get ids or make a stored procedure that accept bulk list and returns resultset with ids. If you absolutely sure that increments are +1 and exactly affectedRows number of rows inserted ( not inserted + updated ) then your code is probably correct, but there might be some other edge cases (like other connection inserting data in parallel if table is not locked)

[killmenot]

@sidorares All conditions are fine except parallel query. That's the reason why I'm asking. Anyway, thank you for the reply.

[sidorares]

no problems @killmenot. At protocol layer only one insert id is returned for bulk insert so if you really need all Ids returned you need to do something in sql as I mentioned (stored procedure or something)

[killmenot]

The way we resolved this issue is the following. We mark inserted bulk data with some group identifier and then use this identifier to select inserted ids. Of course, it adds additional field to the table and a query to retrieve ids but we escape from table locks

[sidorares]

@killmenot my main concern was extra query/roundtrip but It's still better than doing lots of individual inserts

[asngeo]

@sidorares FYI, It looks like the MySQL JDBC driver implements the batch insert ids by retrieving the auto incremental increment: https://github.com/mysql/mysql-connector-j/blob/bf6eb7be997d905a8d71a513bf9e6b3828a91ace/src/main/user-impl/java/com/mysql/cj/jdbc/StatementImpl.java#L1451

Maybe we can do the same thing in mysql2