Question about autoincrement for column type

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jaymoore_299
Forum Contributor
Posts: 128
Joined: Wed May 11, 2005 6:40 pm
Contact:

Question about autoincrement for column type

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

can do two things

1. Research: "LAST_INSERT_ID()"

2. Do a select using

Code: Select all

SELECT MAX(`id`) FROM ..
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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....
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
Post Reply