I have to find the next available id (if there are 5 data in database, I have to get the next available insert place which is 6) in a MySQL database. How can I do that?
I have used MAX(id), but when I delete some rows from the database, it still holds the old max value it didn’t update.
Advertisement
Answer
I don’t think you can ever be sure on the next id, because someone might insert a new row just after you asked for the next id. You would at least need a transaction, and if I’m not mistaken you can only get the actual id used after inserting it, at least that is the common way of handling it — see http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html