Mysql 实现移动端滑动分页

很多移动端分页功能是滑动分页,当 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

结果

这样的结果跟我们数据库的查询结果是一样的