苗火 Nicholas

[MySQL]分页查询的三种方式

2015-10-31 萧 写技术

 SQL中分页查询的方式，记录一下。方式有三种，各自特点就不说了。

 

CREATE TABLE 收藏表(
     `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'primary key',
     `uid` bigint(20) unsigned NOT NULL default 0 COMMENT 'uid',<br>　　  `status` tinyint(3) unsigned NOT NULL default 0 COMMENT 'status',
     `book_id` bigint(20) unsigned NOT NULL default 0 COMMENT 'book Id',
     `create_time` int(11) unsigned not null default 0 COMMENT 'create time',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uid_book_id` (`uid`, `book_id`),<br>　　　 KEY `uid_status` (`uid`, `status`)
)ENGINED=Innodb Auto_increment=1 default charset=gbk COMMENT '用户收藏信息';

 

最容易想到的第一种分页语句是（这也是我们最容易想到的语句）：
select distinct uid from 收藏表 order by uid desc limit 0, 10;
select distinct uid from 收藏表 order by uid desc limit 11, 10;

 

再高级点语句，第二种（$last_min_uid表示上一次读到的最后一个uid）：
select distinct uid from 收藏表 order by uid desc limit 10;
select distinct uid from 收藏表 where uid < $last_min_uid order by uid desc limit 10;

 

最高级的方式
select uid from 收藏表 group by uid order by uid desc limit 10;
select uid from 收藏表 group by uid having uid < $last_min_uid order by uid desc limit 10;

标签: MySQL