Re: Last/highest ID?

[David Garstang]

Can I assume your problem is that you need to insert the file name into the table? If so, I'd suggest the following sequence:

1. Insert an incomplete row (record) into the table.
2. Retrieve the generated ID via LAST_INSERT_ID()
3. Name your file, using the value returned by LAST_INSERT_ID()
4. Update the row with the file name, using the value that was returned by LAST_INSERT_ID().

There are at least two dangers in assuming what value the auto-increment feature will assign:

1. Let's say the highest previous entry was 10, but it got deleted. Now the largest value for that column is 9, but auto-increment will give you 11 for your new insertion, because 10 was previously used.

2. If you are in a multi-threaded (multiple simultaneous DB connections) environment, the following "race condition" can foul you up:

a) The highest current is (let's say) 27, so you assume you'll get 28.
b) While you're renaming your file, another DB connection does an insert into this table, grabbing #28.
c) When you do your insert, you'll get 29.

LAST_INSERT_ID() returns the latest ID that was actually generated *for this DB connection*, so it avoids both of these problems. The extra overhead of doing an insert followed by an update is probably worth it in this case.