What is the difference between oracle paging and mysql paging

Difference: 1. Oracle uses rownum for paging operations. Rownum can only be compared to less than but not greater than, because this method queries first and then sorts; 2. Mysql uses limit for paging operations, and the parameters in limit The value cannot be written as a calculation expression in the statement and needs to be calculated in advance.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the difference between oracle paging and mysql paging

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 row 11 and takes 10 rows, which is the data on page 2.

(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 starting from the yth row (starting line)~x line (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 have never met the conditions, so no results have been 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.

Recommended learning: mysql video tutorial

The above is the detailed content of What is the difference between oracle paging and mysql paging. For more information, please follow other related articles on the PHP Chinese website!