How to perform pagination query in MySQL and return the total count

In MySQL, you can use the LIMIT keyword to achieve pagination in queries, and use the COUNT function to return the total number of results from the query.

Here is an example query statement: Assuming you want to retrieve the first 10 records from a table named “users” and also return the total number of records.

SELECT COUNT(*) FROM users; -- 返回总数

SELECT * FROM users LIMIT 0, 10; -- 分页查询，从第1条开始，共查询10条数据

In the LIMIT clause, the first parameter is the starting position, indicating from which data entry to begin the query, typically starting from 0. The second parameter is the number of records to return, indicating how many data entries to query.

Note: In MySQL, the starting position for a paginated query begins at 0, not 1.