Jump to content

Rely on the AUTO_INCREMENT column to sort rows by order inserted?


shoz

Recommended Posts

Can you?

As mentioned in the thread [url=http://www.phpfreaks.com/forums/index.php/topic,109627.0.html]SQL structure question[/url], I don't know of any reason why you can't. As long as you allow MYSQL to generate the id's it should be in order.

The only thought I have as to why you wouldn't want to only have the id to keep track, is in an instance where you need to combine the data with another table and still keep track of the order. In that case having a DATETIME column would allow you to combine and regenerate the order.

You'd order the data by datetime and id for both tables. The id's will make the sorting somewhat arbitrary between tables, but each tables data should be in the same order.

If the auto_increment column cannot be used then the only way as far as I know to order the data would be to manually make your own counter.
[code]
LOCK table
INSERT based on maximum + 1
UNLOCK TABLE
[/code]

For those wondering why not use the DATETIME column or a more precise time value to keep track. The problem is that even when locking the table it may be possible that two rows get the same time value. ie the LOCK, INSERT and UNLOCK happens fast enough for them to still get the same value
Link to comment
Share on other sites

ugh... i [b]hate[/b] locking tables. as long as you're not truncating tables or resetting the id column, i would agree that it's safe most of the time to just rely on your auto_increment column, but for someone that has THAT much issue with needing to keep strict tabs on the order in which things were inserted, i'd recommend them use postgresql and compare against the oid instead.
Link to comment
Share on other sites

That's a bad, bad idea... even if it will work, from a technical standpoint.  When do you ever actually want the inserted order?  If it's related to date/time, then use a datetime column.  What if you edit these records, and you need to now sort again?  "Inserted order" is a DB internal that should never be exposed.
Link to comment
Share on other sites

[quote author=fenway link=topic=109722.msg442567#msg442567 date=1159383110]
That's a bad, bad idea... even if it will work, from a technical standpoint.  When do you ever actually want the inserted order?  If it's related to date/time, then use a datetime column.  What if you edit these records, and you need to now sort again?  "Inserted order" is a DB internal that should never be exposed.
[/quote]

An example would be posts in a forum. Using a common occurence in these forums. Two users Post at the same time. User1 posts saying "answer is x". User2 posts saying "answer is "y". User2 realizing they're late edits their post and says "The post above mine is a better solution". If the order doesn't remain the same then the information will be misleading.

The situation isn't critically important in this instance, but if the system should have the data ordered by the order of the event then you should be able to keep track of it. Using a DATETIME value will leave it up to the internal storage of the rows to determine the order of two rows that have the same datetime value. EDIT: As far as I know.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.