如何从 MySQL 数据库中选择分页明智的 N 条记录? [英] How to select pagination wise N number of records from MySQL Database?

问题描述

在我最近的一个基于 PHP (CodeIgniter) 的项目中，一个 MySQL 表包含近 30.000 条记录(并且还会增加更多).如果我将这些记录显示为列表，则性能会降低(MySQL 会快速检索所有这些记录，但运行 PHP 循环大约 30.000 次是不可行的).所以我的计划是一次加载 100 条记录并创建一个分页，以便用户可以选择一个页码来加载指定数量的记录.

In one of my recent PHP (CodeIgniter) based projects, a MySQL table contains almost 30.000 records (and will increase more). If I show those records as a list, the performance is reduced (MySQL retrieves all of those records in a quick time, but running a PHP loop for around 30.000 times is not feasible). So my plan is to load 100 records at a time and create a pagination so that the user can select a page number to load that specified number of records.

我可以使用以下查询轻松加载前 100 条记录:

I can load the first 100 records easily using the following query:

SELECT * FROM <table_name> ORDER BY id ASC LIMIT 100

但问题是:当我选择page=2, page=3, ...., page=n.如何编写一个 MySQL 查询来加载第 2 条 100 条记录(对于 page=2)、第 3 条 100 条记录(对于 page=3)、... ... 第 n 条记录(对于 page=n)?

But the problem is: when I select page=2, page=3, ...., page=n. How can I write a MySQL query that will load 2nd 100 records (for page=2), 3rd 100 records (for page=3),... ... nth 100 record (for page=n)?

注意: 请参考 101 号码记录可能没有 101 id，类似的 201 号码记录可能没有 201 id，所以请不要向我建议将取决于的查询主键

N.B: Please refer the 101 number record may not have the id 101, similar 201 number record may not have the id of 201, so please don't suggest me a query that will depend on Primary Key

谁能帮我找出正确的查询?

Can anyone help me to find out the correct query?

• 谢谢

推荐答案

第一百

 SELECT * FROM <table_name> ORDER BY id ASC LIMIT 0, 100

下一百

 SELECT * FROM <table_name> ORDER BY id ASC LIMIT 100, 100

你对下单很细心

限制声明说明:LIMIT 语句不是 WHERE 子句.它不按 id 选择，实际上也不按任何条件选择，(where 子句可以做到这一点)相反，limit 子句只是确保您是返回作为一切"子集的结果的一部分 block.因此为什么每次都提到一个 order by 很重要，这样每个后续调用都会按顺序给你正确的数据块，你可以 'next', 'next', '接下来'通过他们

THE LIMIT STATEMENT EXPLAINED: The LIMIT statement is NOT a WHERE clause. It does not select by id nor in fact by any criteria, (there where clause does that) Instead the limit clause simply ensures that you are returned a piece of the block of results that are subset of "everything". Hence why it is important to mention an order by each time, so that each subsequent call will give you the correct piece of the data block in order, and you can 'next', 'next', 'next' through them

EG:对于无序表this_table:

+-------+-------------+
|  id   |   value     |
+-------+-------------+
|  1    |     bob     |
|  12   |     fish    |
|  112  |     pink    |
|  2    |     cat     |
|  8    |     dog     |
|  56   |     blue    |
|  88   |     grey    |
|  87   |     red     |
+-------+-------------+

选择返回如下:

SELECT * FROM <this_table> ORDER BY id ASC LIMIT 0,5
+-------+-------------+
|  id   |   value     |
+-------+-------------+
|  1    |     bob     |
|  2    |     cat     |
|  8    |     dog     |
|  12   |     fish    |
|  56   |     blue    |
+-------+-------------+

和

SELECT * FROM <this_table> ORDER BY id ASC LIMIT 5,5
+-------+-------------+
|  id   |   value     |
+-------+-------------+
|  87   |     red     |
|  88   |     grey    |
|  112  |     pink    |
+-------+-------------+

注意缺少第 9 行和第 10 行，这是故意的，显示 MySQL 按预期工作

notice the lack of rows 9 and 10 this is deliberate and shows MySQL working as intended

顺便说一句，您还应该考虑在 id 上添加索引，这将大大提高这些选择的速度

incidentally you should also look at adding an index on id this will MASSIVELY increase the speed of these selects

ALTER TABLE <table_name> ADD INDEX `id` (`id`)

这篇关于如何从 MySQL 数据库中选择分页明智的 N 条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！