How to implement paging operation in MySQL

1. Background

What is paging? It means that the amount of data during query is too large. Returning all query results at once consumes network resources and reduces the cost. In terms of query efficiency, it is impossible for users to read thousands of pieces of data at once. So the paging technology came into being. Pagination can display only a specified amount of data.

2. Implementation rules

2.1 Keyword LIMIT

In MySQL, use the keyword LIMIT to implement paging operations. The format is:

LIMIT position offset, number of entries per page;

When the position offset is equal to 0, the statement can be abbreviated as:

LIMIT Number of entries per page;

[Example 1] Query the employee_id and of employees in the employee table employees last_name, salary and department_id information. and displayed at 20 items per page.

# 写法一
SELECT employee_id, last_name, salary
FROM employees
LIMIT 0, 20;

# 写法二
SELECT employee_id, last_name, salary
FROM employees
LIMIT 20;

Among them, the first parameter after LIMIT is the initial offset, that is, from which piece of data to start displaying; the second parameter is how many pieces of data to display on each page.

Displays employees with employee numbers 100~119.

[Example 2] Continuing the above example 1, the above code only displays the data on the first page. If the user now wants to display the data on the second page, what should he do? You can use LIMIT and the first parameter is the initial offset to let the database start displaying the 21st piece of data (because the number of the 1st piece of data is 0, so the number of the 20th piece of data is 20) . As shown in the following code:

SELECT employee_id, last_name, salary
FROM employees
LIMIT 20, 20;

displays employees with employee numbers 120~139.

[Summary] Summarize a paging formula, assuming that the number of data displayed on each page is pageSize, and the current page is pageNo, then there is the following LIMIT Query formula:

LIMIT (pageNo - 1) * pageSize, pageSize;

2.2 Query the x x x record of the database table

[Example 1] Query the data of the 32nd and 33rd employees in the employee table employees.

SELECT employee_id, last_name, salary
FROM employees
LIMIT 31, 2;

2.3 Declaration order (non-execution order) of WHERE…ORDER BY…LIMIT…

The correct declaration order is as shown in the following code:

SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= 5000
ORDER BY salary DESC
LIMIT 0, 10;

3. Expansion

MySQL8.0 has added a new feature for paging, adding an offset after LIMITOFFSET:

LIMIT every Number of page entries OFFSET Initial offset;

Compare the original format, which is nothing more than reversing the position of the initial offset and the number of entries per page, and then removing the comma.

LIMIT Can be used in MySQL, PostgreSQL, MariaDB and SQLite. LIMIT cannot be used in Oracle, DB2, SQL Server!

The above is the detailed content of How to implement paging operation in MySQL. For more information, please follow other related articles on the PHP Chinese website!