CODEWITHSUNDEEP
phpmysql
heliosk
heliosk

Reputation: 1161

mysql - always get MAX id from table

If I have a bunch of inserts going on into a table and I execute a select statement to get the last id, will the select wait until all the inserts are done? Is There some difference if I use MAX or last_insert_id?

Right now I'm using:

SELECT MAX(ID) as myid FROM MY_TABLE WHERE my_column = "my_condition";

Upvotes: 0

Views: 606

Answers (2)

symcbean
symcbean

Reputation: 48357

will the select wait until all the inserts are done?

Nope. Why would it? If the insert queries were issued before the SELECT query and it's a MyISAM table, then yes, you'll get the id of the last insert. That such queries are serialized are the reason that most people use InnoDB for OLTP.

Is There some difference if I use MAX or last_insert_id?

Yes. last_insert_id wil return the last id generated by your session, while MAX(id) will return the greatest integer assigned by any session before your query was run - auto_increment integers do not alwayts increase monotonically.

Unless you know exactly what you are doing, and are deliberately doing something very odd, last_insert_id will give you the answer you are looking for and will be more efficient.

Upvotes: 2

Yuuu
Yuuu

Reputation: 174

If you use MAX, you will always get the greatest number in the database. That is like use a AUTO_INCREMENT.

When you use "last_insert_id" you get the last insert id, so if you insert something in the id 4, but your max id is 10, you have to check if is something between 4 and 10.

I recommend you use "MAX" or configure it correctly the AUTO_INCREMENT in the database

Upvotes: 0

Related Questions