MySQL查询使用ORDER BY+LIMIT+OFFSET时出现重复结果问题

Hey there! I’ve run into this exact pagination duplicate issue before, so let’s break down why it’s happening and how to fix it for your notification system.

Why Duplicates Happen #

The root cause here is unstable sorting. When you use ORDER BY on a field that has duplicate values (like a create_time where multiple notifications are created at the same exact moment), the database doesn’t have a consistent way to order those identical records. Every time you run the query with a different OFFSET, the database might rearrange those same-value records, leading to some showing up on multiple pages or being skipped entirely. Since your SQL Fiddle probably has a small dataset with few (if any) duplicate sort values, you don’t see the issue there—but production data with more volume will trigger it.

Fixes to Try #

1. Add a Unique Identifier to Your ORDER BY Clause

The easiest and most reliable fix is to append a unique field (like your notification table’s primary key id) to the ORDER BY statement. This ensures every record has a definitive, unchanging position in the sorted results.

For example, if your original query looks like this:

SELECT n.*, u.username, t.type_label
FROM notifications n
JOIN users u ON n.recipient_id = u.id
JOIN notification_types t ON n.type_id = t.id
ORDER BY n.created_at DESC
LIMIT 10 OFFSET ?

Update it to:

SELECT n.*, u.username, t.type_label
FROM notifications n
JOIN users u ON n.recipient_id = u.id
JOIN notification_types t ON n.type_id = t.id
ORDER BY n.created_at DESC, n.id DESC
LIMIT 10 OFFSET ?

The n.id DESC (or ASC, depending on your needs) guarantees stable sorting—even if two notifications have the same created_at, their unique IDs will keep them in a fixed order across pagination calls.

2. Check for JOIN-Induced Duplicates

Since you’re working with three tables, it’s possible your JOIN logic is returning duplicate rows for the same notification. For example, if a user has multiple related records in another table, the join might multiply the notification row.

• Double-check your JOIN conditions to make sure they’re only linking one-to-one or one-to-many correctly (without unintended cartesian products).
• If duplicates are coming from joins, you can use DISTINCT to deduplicate results—but be cautious: DISTINCT can add overhead, so fixing the join logic is better if possible. Example:

  SELECT DISTINCT n.*, u.username, t.type_label
  FROM notifications n
  JOIN users u ON n.recipient_id = u.id
  JOIN notification_types t ON n.type_id = t.id
  ORDER BY n.created_at DESC, n.id DESC
  LIMIT 10 OFFSET ?
  

3. Verify Your Offset Calculation

Sometimes the issue isn’t with the SQL itself, but how you’re calculating the OFFSET value. Make sure your pagination math is correct:

• For page 1: OFFSET 0
• Page 2: OFFSET 10
• Page N: OFFSET (page_number - 1) * 10
  A miscalculation here (like using page_number * 10 instead) could lead to overlapping results between pages.

Wrap-Up #

The unstable sorting from duplicate ORDER BY values is almost certainly the culprit here. Adding the primary key to your sort clause should resolve the duplicate results immediately, even with larger datasets.

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