MySQL select after a certain id?

[rockinaway]

Is it possible to do a select statement where I select all the matching values after a particular id? (my id is the primary key if that helps)

[ManiacDan]

Are you looking for some solution other than the greater-than operator?

[rockinaway]

Wow.. why did I not think of that?.. I spent ages thinking aha. So just in a where clause?.. i.e. WHERE id > current_id

[Maq]

Wow.. why did I not think of that?.. I spent ages thinking aha. So just in a where clause?.. i.e. WHERE id > current_id

Yes, you could have just tried it

[rockinaway]

Oh I haven't written the code yet, I'm planning the structure haha But thanks.. completely didn't see it.

[ManiacDan]

If your definition of "after" is "chronological" then you should have an insertDate field on this table so you can do it right.  IDs can be re-numbered or switched.

[rockinaway]

Well I have auto_increment on the id so how would that happen?

[ManiacDan]

you could do it yourself by rebuilding the indexes on the table (though it's very unlikely).  I just think if you want to do date queries, you should be using a date.

[fenway]

Well I have auto_increment on the id so how would that happen?

Don't rely on the absolute value of an ID for anything.

[rockinaway]

Why do you say that?

[ManiacDan]

The ID of a row is metadata, not actual data.  It's supposed to be used to identify a row 3 seconds later when a user clicks "update."  It's not supposed to be used to determine the long-term identity of a piece of data.

 

Back in the day when Fenway and I learned databases, especially MySQL, the ID of a row could actually CHANGE at random.  If you had all your foreign keys set up properly and your code was correct, it wouldn't affect you, but if you thought that User ID 3 was always you, and that User ID 47 was created after User ID 45, you were in for some surprises. 

 

It's not that big of a deal anymore, of course, because database tech has matured, but it's still not good to rely on an auto-number column for absolute long term identity.  The only thing that identifies the data accurately is the data itself.  The auto-numbered keys are for ease of access for your application that sits on top of a database (and in some instances for foreign keys).

 

Apologies to fenway if I made you feel old.

[rockinaway]

I see, thanks a lot for the explanation. You're going to laugh at this but I found that using the id as a method of ordering was actually causing some errors

 

So I added a 'AND time < ...' so using actual values at the same times. Solved the problems. Thanks!

[fenway]

The ID of a row is metadata, not actual data.  It's supposed to be used to identify a row 3 seconds later when a user clicks "update."  It's not supposed to be used to determine the long-term identity of a piece of data.

 

Back in the day when Fenway and I learned databases, especially MySQL, the ID of a row could actually CHANGE at random.  If you had all your foreign keys set up properly and your code was correct, it wouldn't affect you, but if you thought that User ID 3 was always you, and that User ID 47 was created after User ID 45, you were in for some surprises. 

 

It's not that big of a deal anymore, of course, because database tech has matured, but it's still not good to rely on an auto-number column for absolute long term identity.  The only thing that identifies the data accurately is the data itself.  The auto-numbered keys are for ease of access for your application that sits on top of a database (and in some instances for foreign keys).

 

Apologies to fenway if I made you feel old.

I have different complaints about auto-increment -- particularly as they relate to InnoDB and server restarts -- but the fact that you can assign an arbitrary one make it all meaningless, and gaps can filled as well if rows are deleted under certain circumstances.

 

You have to treat this as meta-data, that's true -- consider it a "row pointer", and nothing else -- it's an integer for monotonic convenience, nothing more.

 

@ManiacDan -- not that old, but those problems are still fresh.