很多移动端分页功能是滑动分页,当 api 需要提供分页功能的时候,数据库端需要使用 procedure 存储过程.
1. 使用 workbench 或其他的数据库操作工具新建储存过程
将新建的存储过程命名为 paging
新建存储过程代码
CREATE DEFINER=`dev_pmall`@`%` PROCEDURE `paging`(IN tb_name VARCHAR(128), IN select_str VARCHAR(1024), IN where_str VARCHAR(1024),
IN where_str2 VARCHAR(1024), IN order_by_str VARCHAR(1024), IN counter INT)
BEGIN
SET @current = 0;
SET @row_number = 0;
SET @sql1 = CONCAT('SELECT t2.row_number into @current
FROM
(SELECT
(@row_number := @row_number + 1) AS row_number,
t.*
FROM (SELECT * FROM ', tb_name, ') t
WHERE ', where_str, '
ORDER BY ', order_by_str, '
) t2
WHERE ', where_str2, ' ;');
PREPARE sql1 FROM @sql1;
EXECUTE sql1;
SET @row_number = 0;
SET @sql2 = CONCAT('SELECT ', select_str, '
FROM
(SELECT
(@row_number := @row_number + 1) AS row_number,
t.*
FROM (SELECT * FROM ', tb_name, ') t
WHERE ', where_str, '
ORDER BY', order_by_str, '
) t2
WHERE t2.row_number > ', @current, ' LIMIT 0, ', counter, ';');
PREPARE sql2 FROM @sql2;
EXECUTE sql2;
END封装的存储过程使用类(Laravel/Lumen 框架)
<?php
/**
* Created by PhpStorm.
* User: nwei
* Date: 2018/6/7
* Time: 12:05
*
* .--, .--,
* ( ( \.---./ ) )
* '.__/o o\__.'
* {= ^ =}
* > - <
* / \
* // \\
* //| . |\\
* "'\ /'"_.-~^`'-.
* \ _ /--' `
* ___)( )(___
* (((__) (__))) 高山仰止,景行行止.虽不能至,心向往之.
*
*/
namespace App\Tools;
use Illuminate\Support\Facades\DB;
class CursorPaging {
protected $table_name = null;
protected $select_str = null;
protected $where_str = null;
protected $where_str2 = null;
protected $order_by_str = null;
protected $since = null;
protected $count = null;
/**
* CursorPaging constructor.
* @param $table_name
* @param $select_str
* @param $where_str
* @param $where_str2
* @param $order_by_str
* @param $since
* @param $count
*
* 通过 pop 商城存储过程修改,该 pop 使用 CI 框架,代表 sql 示例
*
* if ($sec['since']) {
$query = $this->db2->query("
CALL `paging`(
' {$tb_name} ',
' product_id,name,sub_name,status,image,pd_price,stocks ',
' status = 1 and device = 2 and name like ''%{$keyword}%'' ',
' product_id = {$sec['since']} ',
' date_added desc, product_id desc ',
{$sec['count']}
);");
} else {
$query = $this->db2
->select('product_id,name,sub_name,status,image,pd_price,stocks')
->where('status', 1)
->where('device', 2)
->like('name', $keyword)
->order_by('date_added desc, product_id desc')
->limit($sec['count'], 0)
->get('v_wx_product');
}
*
*
* 未集成前示例
*
if ($since) {
$tb_name = "sms";
$select_str = "*";
$where_str = "1=1"; //条件
$where_str2 = " id = ".$since; //游标条件
$order_by_str = " id desc ";
$counter = $count;
$rs = DB::select('call paging(?, ?, ?, ?,?,?)',[$tb_name,$select_str,$where_str,$where_str2,$order_by_str,$counter]);
} else {
$query = "select * from sms order by id desc limit 0,".env("COUNT");
$rs = DB::select($query);
}
return $this->jsonData("0","ok",$rs);
*
*/
function __construct($table_name,$select_str,$where_str,$where_str2,$order_by_str,$since,$count) {
$this->since = isset($since)?$since:null;
$this->count = isset($count)?$count:env("COUNT"); //选填
$this->table_name = $table_name; //必填
$this->select_str = isset($select_str)?$select_str:"*";
$this->where_str = isset($where_str)?$where_str:" 1=1 ";
$this->where_str2 = $where_str2; //必填
$this->order_by_str = $order_by_str; //必填
}
/**
* 利用游标分页
* @return mixed
*/
public function cursor_paging(){
$rs = null;
if ($this->since) {
$params = [
$this->table_name,
$this->select_str,
$this->where_str,
$this->where_str2,
$this->order_by_str,
$this->count
];
$rs = DB::select('call paging(?, ?, ?, ?,?,?)',$params);
} else {
$query = "select ".$this->select_str." from ".$this->table_name." where ".$this->where_str." order by ".$this->order_by_str." limit 0,".$this->count;
$rs = DB::select($query);
}
return $rs;
}
}构造函数参数说明
构造函数中的第一个参数是表名,第二个参数是要查询的字段名,不传参默认为 '*',第三个参数是查询条件,不传参默认为 '1=1',第四个参数是游标条件,第五个参数是排序方式,第六个参数是开始游标,第七个参数为每页的数量.
使用示例
public function getProducts($request,$product_type){
$since = !empty($request->input("since"))?$request->input("since"):null;
$where_str = " status = 1 and weight = 1 and product_type = ".$product_type ." ";
$where_str2 = " id = ".$since ." ";
$order_by_str = " updated_at desc ";
$table_name = " product ";
$select_str = null;
$rs = [];
try{
$procedure = new CursorPaging($table_name,$select_str,$where_str,$where_str2,$order_by_str,$since,null);
$rs = $procedure->cursor_paging();
}catch (\Exception $exception){
$rs = false;
operate_log("0","前端获取商品列表失败",$exception);
}
return $rs;
}结果
这是没有任何参数的请求结果, 每页两条数据,此时它的游标位在表中 id=1 的位置上,第二次请求时我们的路由应该变成这样
http://localhost:30017/api/v1/front/products?since=1结果
这样的结果跟我们数据库的查询结果是一样的
