Jump to content


Photo

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


  • Please log in to reply
5 replies to this topic

#1 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 27 September 2006 - 05:35 PM

Can you?

As mentioned in the thread SQL structure question, 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.
LOCK table
INSERT based on maximum + 1
UNLOCK TABLE

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

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 27 September 2006 - 05:41 PM

ugh... i hate 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.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 September 2006 - 06:51 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 27 September 2006 - 06:58 PM

...that's why i put in the disclaimer for someone that has THAT much issue with needing to keep strict tabs on the order in which things were inserted ;-) ... i agree that a date/time sort should be sufficient
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 27 September 2006 - 07:13 PM

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.


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.

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 September 2006 - 07:17 PM

If you care that much, you can ORDER BY datetime, uid.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users