Page 1 of 1

Question about autoincrement for column type

Posted: Mon Jan 30, 2006 5:18 pm
by jaymoore_299
I have never used this feature before and I have simple question about it.

If I insert new data, a new row will be made in the table with the next available id number.
However how do I know what the number assigned to this row was without having to search through the table looking for the highest id number?

Posted: Mon Jan 30, 2006 5:24 pm
by John Cartwright
can do two things

1. Research: "LAST_INSERT_ID()"

2. Do a select using

Code: Select all

SELECT MAX(`id`) FROM ..

Posted: Mon Jan 30, 2006 5:54 pm
by raghavan20
Jcart wrote:can do two things

1. Research: "LAST_INSERT_ID()"

2. Do a select using

Code: Select all

SELECT MAX(`id`) FROM ..
This is a common problem. But I do not think it is good to use last_insert_id() because if two records are inserted one after the other in quick succession then there is a good chance to yield a wrong value.

The second method is not foolproof as well.

The best way would be to use a set of fields which would help identify a record other than the primary key since we do not know it anyway...

Posted: Mon Jan 30, 2006 10:42 pm
by josh
Contrary to popular beleif, grabbing the insert id via last_insert_id() or the php api for it mysql_insert_id() is on a per-connection basis. As long as you don't have persistent connections you are fine (it may be foolproof with pconnections as well I don't know). Grabbing the max value on the field is the only one of the methods that could ever produce an undesired result.


Reference: mysql manual
For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0)

Posted: Tue Jan 31, 2006 5:18 am
by raghavan20
I just have a basic doubt...when we allow users to use a common mysql username through PHP scripts to access mysql db, we use only one mysql account but the connections are different ... are not they? Any number of connections (or specified in .ini file) can be made with a single user name ... is not it? So, here to this question, there would be one connection ..is not it...but what if someone uses the same session and updates two forms....

Posted: Tue Jan 31, 2006 6:18 am
by jayshields
Sorry to hijack your thread, but I also have a question about auto incrementing ID fields in MySQL.

I nearly always use a generic auto-incrementing column as a primary key in a table (is it bad practice to do this and then never insert into that column and let MySQL do the work?). If I insert 3 rows into the table the ID's are 1, 2, 3 respectively as you would expect, If I then delete the row with ID = 3 and then insert a new row, the new row ID is 4, it's never 3, although 3 is now available.

Is this supposed to happen? Is it logical?

I'm not particularly bothered about it, I just think it would look alot neater if it always started at 1 and went through to the last ID without missing any numbers.

The only reason I can think of never using a previously used ID again is incase the user refers to the ID specifically and on a regular basis, and someone deletes that row, and inserts a new row which takes over the old ID, the original user will then be puzzled and think he has chosen the wrong ID to look at. Whereas if the row was just deleted MySQL would return no rows and make more sense.

Is that the only reason?

Rethinking my post, I realised that if MySQL always used the lowest available ID it would have to look at every single ID ever used in the table and pick out the earliest hole, whereas the method in place just uses the next pre-set index does it not?

Posted: Tue Jan 31, 2006 6:35 am
by raghavan20
you almost had the answer...it is an overhead to look for deleted ids rather it is easier to allocate the next highest value for the id...it is always better to leave the Mysql do the job...