Detailed explanation of the difference between Mysql and Oracle paging

Mysql uses limit paging and Oracle uses rownum paging. Let me introduce the difference between Oracle and Mysql paging through this article. Friends who need it can refer to it

1. Mysql uses limit Paging

select * from stu limit m, n; //m = (startPage-1)*pageSize,n = pageSize

PS:

(1) The first parameter value m represents the starting line, and the second parameter represents How many lines to take (page size)

(2)m= (2-1)*10+1,n=10, which means limit 11,10 starts from line 11, takes 10 lines, that is, page 2 data.

(3) The m and n parameter values ​​cannot be written in calculation expressions in the statement. The values ​​must be calculated before writing them in the statement.

2. Oracle uses rownum for paging

##

select * from (
select rownum rn,a.* from table_name a where rownum <= x
//结束行，x = startPage*pageSize
)
where rn >= y; //起始行，y = (startPage-1)*pageSize+1

PS:

（1 )>= y, <= x means from line y (starting line) to line x (ending line).

(2) rownum can only be compared to less than, not greater than, because rownum is queried first and then sorted. For example, your condition is rownum>1. When the first piece of data is queried, rownum is 1, then Ineligible. The 2nd, 3rd... are similar, but they never meet the conditions, so no result is ever returned. Therefore, you need to set an alias when querying, and then call the alias to determine the greater than value after the query is completed.

Summarize

The above is the detailed content of Detailed explanation of the difference between Mysql and Oracle paging. For more information, please follow other related articles on the PHP Chinese website!