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";