如何用Knex.js按100行分批查询表数据并获取剩余行？

Hey there! The issue you're hitting with ID-based filtering makes total sense—when IDs aren't continuous, using where('id', '>', X) can't reliably split your data into chunks. Let's fix this with proper pagination that works regardless of ID gaps, and covers that final "remaining rows" case perfectly.

Instead of relying on ID values, use offset() and limit() in Knex. These methods work based on the ordered result set's row count, so gaps in IDs won't throw off your chunking.

First, a critical note: Always add an orderBy() clause #

Without a consistent sort order, the database might return rows in unpredictable order across queries. Use a stable field like your primary key (id) or a creation timestamp (created_at) to ensure pagination works as expected.

1. Get the first 100 rows #

knex('table-name')
  .select('*')
  .orderBy('id') // Use your preferred stable sort field here
  .limit(100);

2. Get the next 100 rows (rows 101-200) #

knex('table-name')
  .select('*')
  .orderBy('id')
  .offset(100) // Skip the first 100 rows
  .limit(100);

3. Get the third set of 100 rows (rows 201-300) #

knex('table-name')
  .select('*')
  .orderBy('id')
  .offset(200) // Skip the first 200 rows
  .limit(100);

4. Get all remaining rows (rows 301-520) #

You don't need to specify a limit() here—Knex will automatically return all rows starting from the offset point:

knex('table-name')
  .select('*')
  .orderBy('id')
  .offset(300); // Skip the first 300 rows, return everything after

Why this works better than ID filtering #

• No ID gap issues: Offset/limit counts rows directly, so missing IDs won't cause you to skip valid rows or return fewer than 100 entries per chunk.
• Consistent ordering: The orderBy() clause ensures each query picks up exactly where the last one left off.
• Simplicity for small datasets: For your 520-row table, this approach is lightweight and easy to implement without extra logic.

If you were working with a huge dataset (100k+ rows), offset could cause performance hits (since the database has to scan all skipped rows). But for your use case, this is the perfect solution.

内容的提问来源于stack exchange，提问作者user11042502