1 传统 LIMIT 初始位置,记录数 查询

SELECT * FROM `user` OREDER BY ID LIMIT 100,10

缺点: 数据量大时,初始位置越大,耗时越长

2 配合 WHERE 查询

（游标分页）

SELECT * FROM `user` WHERE ID > 100 ORDER BY ID LIMIT 10
-- 这里ID为自增主键(int型)

缺点: 1.ID可能不是连续的;2.只能查询上一页,下一页,最后一页,不能跳页

3 实例

前端采用layui table展示, 并增加参数 [最大ID,最小ID,上一次查询的页码]给后台

<!-- html 代码-->
<form class="layui-form"><div class="layui-inline layui-show-xs-block"><input type="text" name="status" class="layui-input"></div><button class="layui-btn" lay-submit="" lay-filter="search"><i class="layui-icon">&#xe615;</i></button>
</from>
<table id="demo" lay-filter="demo"></table>

// js 代码
layui.use(['table','form'],function(){var form = layui.form,table = layui.table;// 本页最大ID, 本页最小ID, 上一次查询的页码var maxid = 0, minid = 0, lastp = 1;table.render({elem:'demo',id:'demo',url:'',title:'',height:'',page:{ // 这里将默认的显示方式改掉,去掉跳页功能,并且只显示1个页码layout:['limit','count','prev','page','next'],groups:1, //只显示 1 个连续页码},limits: [10,15, 25],limit:10,where:{maxid:function(){return maxid},minid:function(){return minid},lastp:function(){return lastp},},cols:[[{field:'ID',title:'ID'},{field:'STATUS',title:'STATUS'}]],done:function(res,curr,count){//res: 如果是异步请求数据方式，res即为你接口返回的信息; 如果是直接赋值的方式，res即为：{data: [], count: 99} data为当前页数据、count为数据总长度// curr: 当前页码// count: 数据总量var that = this.elem.next();lastp = curr;res.data.forEach(function(item,index){// minid 和maxid赋值if(index==0){maxid = minid = item.ID;}if(maxid < item.ID) maxid = item.ID;if(minid > item.ID) minid = item.ID;// ID为1时给该行中STATUS栏染色if (item.ID == 1) {that.find(".layui-table-box tbody tr[data-index='" + index + "'] td[data-field='STATUS']").css({ "background-color": "#FE838E", 'color': 'white' });}// ID为2时给整行将整行字体颜色变红if(item.ID == 2){that.find(".layui-table-box tbody tr[data-index='" + index + "']").css({'color': 'red' });}});}});table.on('row(inspectlist)', function(obj){/* 监听行单击事件（双击事件为：rowDouble）*/obj.tr.addClass('layui-table-click1').siblings().removeClass('layui-table-click1');/*标注选中样式 layui-table-click1(自定义)*/});form.on('submit(search)',function(data) {table.reload('demo', {page:{curr:1,layout:['limit','count','prev','page','next'],groups:1,first:false,last:false},where:{status:data.field.status,maxid:function(){return maxid},minid:function(){return minid},lastp:function(){return lastp},}});return false;});
});

按ID倒序输出结果

// PHP 代码// where条件拼接
$where = '';
!empty($_GET['status']) ? $where .= "AND STATUS = '{$_GET['status']}'":'';
$where = trim($where, "AND ");
$where = $where == "" ? $where:"WHERE ".$where;// 查询总条数
$sql = "SELECT COUNT(*) FROM `user` {$where}";
$count = $db->query($sql);$limit = $_GET['limit']; // 每页显示条数
$countpage = ceil($count / $limit); // 总页数
$page = $_GET['page']; // 请求页码 (最小为1)
$maxid = $_GET['maxid']; // 当前页最大idx
$minid = $_GET['minid']; // 当前页最小idx
$lastp = $_GET['lastp']; // 上一次的页码if($page <= 1 || $page >= $countpage) {// 第1页if($page <= 1){$sql = "SELECT * FROM `user` {$where} ORDER BY ID DESC LIMIT {$limit}";$data = $db->query($sql);}// 最后一页if($page >= $countpage){$lm = $count - ($countpage-1) * $limit;$sql = "SELECT * FROM (SELECT * FROM `user` {$where} ORDER BY ID ASC LIMIT $lm)a ORDER BY a.ID DESC";$data = $db->query($sql);}
} else {// 上一页if($page < $lastp){$where .= $where == "" ? "IDX > ".$maxid : " AND IDX > ".$maxid;$sql = "SELECT * FROM (SELECT * FROM `user` {$where} ORDER BY ID ASC LIMIT $limit)a ORDER BY a.ID DESC";$data = $db->query($sql);}// 下一页if($lastp < $page){$where .= $where == "" ? "IDX < ".$minid : " AND IDX < ".$minid;$sql = "SELECT * FROM `user` {$where} ORDER BY ID DESC LIMIT {$limit}";$data = $db->query($sql);}
}
$arr = ['code'=>0,'msg'=>'','count'=>$count,'data'=>$data
];
echo json_encode($arr);

按ID正序输出

// 第一页
$sql = "SELECT * FROM `user` ORDER BY ID ASC LIMIT 10";
// 最后一页
$lm = $count - ($countpage-1) * 10;
$sql = "SELECT * FROM (SELECT * FROM `user` ORDER BY ID DESC LIMIT $lm)a ORDER BY a.ID ASC";
// 上一页
$sql = "SELECT * FROM (SELECT * FROM `user` WHERE ID < {$minid} ORDER BY ID DESC LIMIT 10)a ORDER BY a.ID ASC";
// 下一页
$sql = "SELECT * FROM `user` WHERE ID > {$maxid} ORDER BY ID ASC LIMIT 10";