How to get SQL data with a query starting from a specific row?

Question

MySQL

I have a table with the following fields

• id - random character string, primary key
  
• date - date accurate to the day, non-unique
  
• ...
  

Data is retrieved from it in portions sorted by date: After processing the first request, the id of the last received row is stored. Question: How can I indicate in the next request that I need to start from a line with a specific id ? It only comes to mind to remember the number of retrieved rows and then do , but this will break if new records with a later date appear in the time between requests.
SELECT * FROM `table` ORDER BY `date` DESC LIMIT 4


LIMIT $gotten, 4

Answer 1

Abandon the approach "hell-then-how will we design a database, and then we will zadolbyvat it with complex SQL queries that consume exorbitant resources on trivial tasks." If id is indeed "a string of random characters", then there is zero useful information in it. In this case, refactor and replace id with an auto-incrementing integer field with an index and make the selection as simple as WHERE id > :thePreviousMaxId. If the id still has meaningful information that is used not only to ensure uniqueness, add an auto-incrementing alternative key, thereby reducing the solution to the previous option. In any case, it will be more efficient than remembering previously received id

Answer 2

You can take it with an "overlap". Since a day is not unique, collisions are possible within this day.
You will have to remember all the ids of the last day of the sample.
In the next step, you need to include the last day in its entirety, and exclude lines with remembered id from it.

Answer 3

If you need to save the state, then either an intermediate / temporary table or, if there are not so many records, request the id's of All records and then ask by id through IN (). Alternatively, you can also update labels for all records, and when there is a bypass, new records will not have this label, which means they will not participate in the selection with a limit, but this is for one client, if the daemon needs to bypass all records.

Answer 4

LIMIT $gotten, 4

- just an option. If it is necessary that the selection does not move when new fields are added, remember the largest value of date at the first request, add "`date` <= $maxdate" to the subsequent ones. in WHERE.