Re^3: How to find the highest number in a mysql table

That's typically avoided because of the race condition.

You could insert the row with a "not yet published" flag, and only create the file after the insert. Or have a separate table with just the auto-increment field and allocate ids whenever you want to use for the filenames and insertion into the main table.

--
Online Fortune Cookie Search

Comment on Re^3: How to find the highest number in a mysql table

Replies are listed 'Best First'.
Re^4: How to find the highest number in a mysql table by sulfericacid (Deacon) on May 10, 2008 at 02:42 UTC
++. Interesting approach. Get the database set up first, determine what the last id was, then continue processing. Didn't think of going this route. "Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us" sulfericacid	[reply]
Re^4: How to find the highest number in a mysql table by pc88mxer (Vicar) on May 10, 2008 at 06:24 UTC
ysth writes: You could insert the row with a "not yet published" flag, and only create the file after the insert. Instead of keeping the image data in a file, consider storing it in the database in the same row that describes it. Keeping a file system and database in sync can tricky. You have to be prepared to handle files which don't have database entries and database entries which don't refer to files. That's why it can be simpler just to store your file data in your database (e.g. in a BLOB column.) Just something to think about... Also, to avoid the race condition that ysth refers to, try to perform the determination of the last number used and the updating of the new row in one SQL statement: `-- create the new image row with blank gallery_id: INSERT INTO images ... VALUES (...); -- atomically update the gallery_id field UPDATE images SET gallery_id = (SELECT 1+MAX(...) FROM images) WHERE + ...;` [download] Another possibility is to do it all in one `INSERT` statement: `INSERT INTO images (gallery_id, title, description, fstop) SELECT 1+MAX(...), 'title', 'description', 'fstop' FROM images;` [download] You'll run into a race condition if you have perl figure out the next id and then use that id in a subsequent `INSERT` or `UPDATE` statement.	[reply] [d/l] [select]