MySQL:根据另一个表上的数据分页

Question

我有一个具有以下结构的数据库

• 项目表: itemID，itemName
• Ads表: AdsId，itemID

我想对items表进行分页，每个页面中有10项，但首先必须检索I位于Ads表中的项，然后检索其他项。

我知道我必须使用limit，例如：

SELECT * FROM Items LIMIT $offset, $no_of_records_per_page

请允许我将其与join一起用adsTable编辑，例如：

SELECT * FROM Items inner join Ads on Ads.itemID = Items.itemID
LIMIT $offset, $no_of_records_per_page

但我怎样才能实现我所描述的呢？提前谢谢。

Answer 1

方法#1

您可以创建一个名为itemID_paginator的临时表，并将其分页

CREATE TABLE items_paginator AS SELECT itemID from Items WHERE 1=2;
ALTER TABLE items_paginator ADD PRIMARY KEY (itemID);
INSERT INTO items_paginator SELECT itemID from Items;

然后，针对itemID_paginator分页并将其加入Ads。

SELECT A.*,B.* FROM 
(
    SELECT itemID FROM itemID_paginator ORDER BY ItemID
    LIMIT $offset, $no_of_records_per_page
) PG
INNER JOIN Items A ON PG.ItemID = A.ItemID
INNER JOIN Ads   B ON A.ItemID  = B.ItemID;

方法#2

如果您不喜欢使用临时表，那么直接对LIMIT表执行Items。

SELECT A.*,B.* FROM 
(
    SELECT * FROM Items ORDER BY ItemID
    LIMIT $offset, $no_of_records_per_page
) A INNER JOIN Ads B ON A.ItemID  = B.ItemID;

我以前在以下文章中提出了这些方法：

• Jan 31, 2017：在从数百万行中选择较高偏移量时缓慢查询
• Dec 21, 2013：通过简单的子查询在大表中使用偏移量
• Feb 21, 2013：MYSQL是否在限制之前加入？
• May 16, 2011：从联接表中获取单个行

试试看！

Answer 2

在使用ORDER BY时，确实应该使用LIMIT。否则，你得到的行将是不可预测的。

是的，JOIN工作。可以这样想：JOIN以指定的方式组合表，并(逻辑上)创建一个包含组合内容的新表。JOIN完成后，WHERE、GROUP BY、HAVING、ORDER BY和LIMIT按该顺序“执行”。

我说，“逻辑上”，因为优化器有很多技巧使查询更有效，而不是盲目地创建临时表，然后过滤，然后(等等)。

SQL以严格的方式指定您想要的内容。优化器可以自由地重新排列事物，只要它得到相同的答案。

OFFSET显然是进行分页的方法。但是它会导致错误(不是在MySQL中，而是在它在网站上的应用中)。性能受到影响，因为跳过的行实际上是逐个跳过的。更多：http://mysql.rjweb.org/doc.php/pagination