mysql选择列表后的特定id

I'm trying to find a solution to select a list of rows coming after a certain Id from an ordered list.

For example, first I select 1000 rows. Then, on a subsequent request, i want to fetch another 1000 rows coming from after the last id of the first request. I know i can do it with limit, but suppose there has been 100 rows added between the first and second request, there will be 100 rows that will be from the first request.

Both queries will be ordered by the date of the entries.

Here's an example of the query I thought of:

$query = "SELECT * FROM table WHERE id AFTER $id ORDER BY date DESC";

$query = "SELECT * FROM `table` WHERE `id` > '$id' ORDER BY `date` DESC LIMIT 1000";

Two ways to do this:

WHERE

"SELECT * FROM `table` WHERE `id` > '$id' ORDER BY `date` DESC LIMIT $length"

LIMIT

"SELECT * FROM `table` LIMIT $start, $length"

You're asking about logic, not code so here it is.

The first request selects the first 1000.

$query = "SELECT * FROM the_table ORDER BY `date` DESC LIMIT 0,1000";

NB date is a reserved word so needs escaping if you've called a column "date" which you shouldn't.

$rs=$db->selectMany($query); // replace this with however you select the rows. $rs is results set

Do stuff with PHP and save the maximum id. They may not be in order.

$maxid=0;

foreach ($rs as $r){
    // whatever you need to do with your results
    $maxid=max($maxid, $r->id);
}

Your subsequent select uses the last id

$query = "SELECT * FROM the_table WHERE id > $maxid ORDER BY date DESC LIMIT 0,1000";

BUT you need to take note that you're ordering by date and using id to find a breakpoint which sounds like it would cause data to be missed.

Perhaps you mean to use WHERE`date`> $maxdate? If so you can figure that out from the code given.

$query = "SELECT * FROM table WHERE id > $id ORDER BY date LIMIT 1000";