MySQL多對(duì)多查詢?cè)趯?shí)際應(yīng)用中非常常見，但是當(dāng)數(shù)據(jù)量比較大的時(shí)候，需要進(jìn)行分頁(yè)處理才能更好的展示數(shù)據(jù)。下面我們來(lái)介紹一下如何使用MySQL進(jìn)行多對(duì)多分頁(yè)查詢。

-- 例子表結(jié)構(gòu)
CREATE TABLE `album` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `artist` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `album_artist` (
`album_id` int(11) NOT NULL,
`artist_id` int(11) NOT NULL,
PRIMARY KEY (`album_id`,`artist_id`),
KEY `artist_id` (`artist_id`),
CONSTRAINT `album_artist_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `album` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `album_artist_ibfk_2` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 查詢語(yǔ)句
SELECT a.*, GROUP_CONCAT(ar.name SEPARATOR ', ') AS artists
FROM (
SELECT album.*
FROM album
ORDER BY album.id DESC
LIMIT 10 OFFSET 20
) a
LEFT JOIN album_artist aa ON a.id = aa.album_id
LEFT JOIN artist ar ON aa.artist_id = ar.id
GROUP BY a.id
ORDER BY a.id DESC;

上面的查詢語(yǔ)句中，我們首先通過(guò)子查詢獲取需要的數(shù)據(jù)，然后再進(jìn)行多對(duì)多的關(guān)聯(lián)查詢。在GROUP_CONCAT函數(shù)的幫助下，我們可以獲取到每個(gè)專輯對(duì)應(yīng)的藝術(shù)家列表，并通過(guò)LIMIT和OFFSET關(guān)鍵字實(shí)現(xiàn)分頁(yè)。最終結(jié)果按照專輯號(hào)進(jìn)行降序排列。