MySQL中按行号查询指定行数据的正确语法实现

Got it, let's work through this MySQL row number issue you're hitting. The problem with your original attempts is that window functions like ROW_NUMBER() can't be used directly in the WHERE clause—MySQL runs the WHERE filter before applying window functions, so those conditions don't behave as you'd expect.

Here are two reliable, straightforward ways to get the specific row you need:

1. Use a Subquery/CTE to Calculate Row Numbers First #

Wrap the row number calculation in a subquery (or CTE, if you're on MySQL 8.0+) so you can filter on the computed row number in the outer query.

With CTE (MySQL 8.0+) #

This is cleaner and easier to read:

WITH numbered_rows AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY REFNUM) AS row_num
    FROM MYTABLE
)
SELECT * FROM numbered_rows WHERE row_num = 2;

With Subquery (Works for Older MySQL Versions) #

If you're stuck on a version before 8.0, use a subquery instead:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY REFNUM) AS row_num
    FROM MYTABLE
) AS sub_query
WHERE sub_query.row_num = 2;

2. Use LIMIT + OFFSET (Simpler for Single Rows) #

If you just need to fetch the Nth row and don't require the explicit row number value, this method is more concise. Critical note: Always include ORDER BY—without it, MySQL doesn't guarantee a consistent row order, so your "2nd row" might change randomly between queries.

SELECT * FROM MYTABLE ORDER BY REFNUM LIMIT 1 OFFSET 1;

• LIMIT 1 tells MySQL to return only 1 row
• OFFSET 1 skips the first row, so you get the 2nd one (adjust the offset for other rows: e.g., OFFSET 4 for the 5th row)

Key Reminder #

Never skip the ORDER BY clause! MySQL tables don't have a fixed "natural" order. Without sorting, the row you retrieve could be arbitrary, depending on how data is stored or cached.

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