PostgreSQL中如何分段获取排序后的指定用户数据

Hey there! Let's walk through how to implement this pagination requirement, covering both the SQL side and the interface handling.

1. SQL Pagination Implementation #

First, let's ditch SELECT * and explicitly specify the columns we need (id, user_name, age) for better performance and clarity. We'll use database-specific syntax to fetch the desired ranges, plus an extra safeguard for stable sorting:

For MySQL/MariaDB/SQLite #

Use LIMIT and OFFSET to control which rows are returned:

• 1-10 rows:

SELECT id, user_name, age
FROM users
WHERE user_name = 'Andrew'
ORDER BY age DESC, id DESC -- Add id to fix sorting inconsistencies if ages match
LIMIT 10 OFFSET 0;

• 11-20 rows:

SELECT id, user_name, age
FROM users
WHERE user_name = 'Andrew'
ORDER BY age DESC, id DESC
LIMIT 10 OFFSET 10;

• 21-30 rows:

SELECT id, user_name, age
FROM users
WHERE user_name = 'Andrew'
ORDER BY age DESC, id DESC
LIMIT 10 OFFSET 20;

For PostgreSQL #

You can use the same LIMIT/OFFSET syntax above, or the more explicit FETCH NEXT syntax:

• 11-20 rows example:

SELECT id, user_name, age
FROM users
WHERE user_name = 'Andrew'
ORDER BY age DESC, id DESC
OFFSET 10 FETCH NEXT 10 ROWS ONLY;

For SQL Server #

Use OFFSET ... FETCH NEXT for pagination:

• 21-30 rows example:

SELECT id, user_name, age
FROM users
WHERE user_name = 'Andrew'
ORDER BY age DESC, id DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Important Note: Adding id DESC to the ORDER BY clause ensures stable sorting. If multiple users share the same age, this prevents duplicate or missing rows across pages.

2. Handling the /get-users/:from/:to Interface #

Your interface accepts from and to as path parameters representing the row range (e.g., 1/10, 11/20). Here's how to map these to your SQL query safely:

• Calculate the offset: offset = from - 1 (since SQL offsets start at 0, not 1)
• Calculate the limit: limit = to - from + 1 (this will equal 10 for all your required ranges)
• Always use parameterized queries to avoid SQL injection—never directly plug from/to values into your SQL string.

Example Logic (Pseudocode) #

// Example for Node.js/Express
app.get('/get-users/:from/:to', (req, res) => {
  const { from, to } = req.params;
  const fromVal = parseInt(from);
  const toVal = parseInt(to);

  // Validate parameters first
  if (fromVal < 1 || toVal > 30 || fromVal > toVal) {
    return res.status(400).send('Invalid row range');
  }

  const offset = fromVal - 1;
  const limit = toVal - fromVal + 1;

  // Parameterized query using pg library for PostgreSQL
  const query = `
    SELECT id, user_name, age
    FROM users
    WHERE user_name = 'Andrew'
    ORDER BY age DESC, id DESC
    LIMIT $1 OFFSET $2;
  `;

  db.query(query, [limit, offset])
    .then(result => res.json(result.rows))
    .catch(err => res.status(500).send(err.message));
});

3. Key Considerations #

• Avoid SELECT *: Explicit column selection reduces unnecessary data transfer and prevents unexpected schema changes from breaking your app.
• Stable Sorting: As mentioned earlier, adding a unique column like id to your sort order guarantees consistent pagination results when rows have identical age values.
• Performance: For small datasets (like your 30 rows), OFFSET works perfectly. For larger datasets, consider keyset pagination (using the last row's age and id to fetch the next page) to avoid performance hits from scanning large offset ranges.

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