I've a credit card applicatoin which collects data right into a mysql table. The table doesn't have unique id column, and so i can't reference a particular row by id.
I wish to write a dump application which each and every day dumps the brand new rows put into the table to upload them elsewhere. I possibly could get it done with the addition of a distinctive id area and storing the final id left, but I'd rather not add an id column just for your towards the table.
And So I thought I store the amount of rows within the table at each dump and employ time being an offset the next time the table is left (choose * from table limit verylargenumber offset x). Obviously, it really works only when there's an assurance new rows always placed in the finish on the table, so that all new rows is going to be following the offset.
I suppose I'm able to depend on that. Shall We Be Held right?
No this is not the situation. The database will move stuff around to optimize making queries faster. You would need to add a purchase by clause for your query to make sure any kind of order. You need to certainly attempt to add a distinctive id for your table.
No, you are not. There's no certainty regarding the order where the engine will return the rows. A table with no unique ID is usually not this type of godo idea anyways. Within this situation, you certainly have sufficient cause to make use of one.
Much like personal files system, unless of course the table is enhanced or defragmented, erased data will release a "slot" where new data is going to be placed. It is not always appended towards the finish on the table.
So if you have 3 rows: A, B, C
Should you remove B, your table will basically seem like A, [free space], C
If you place D to your table, it'll now seem like: A, D, C
Your best choice is by using a distinctive auto incrementing key. This can also accelerate queries.