EasySwoole开发首页——Mysql方案

Author：羡仙.
发布时间：May 9, 2019
727views
No comments
9484字数
Categories： Mysql EasySwoole Swoole DataBase Basics

我目前在使用EasySwoole开发一个视频网站,目前先做API接口.纯属个人兴趣爱好,有不足的地方可以指出来哈.关于首页的话有几个方案我都会去做一下,也方便大家去了解使用
首先创建首页的控制器Api\HttpController\Api\Index.php

<?php

namespace App\HttpController\Api;

use App\HttpController\Api\Base;

class Index extends Base
{
    public function navigation()
    {
        $data = \Yaconf::get('navigation');
        $data = $data['cat'];
        return $this->writeJson(0,'ok',$data);
    }
}

首页的话肯定会有栏目,emmmm又或者叫导航.管它是啥呢！反正你肯定能懂我意思的！我们可以把那个玩意写入配置文件中用的是Yaconf哟
在配置ini目录下创建`navigation.ini文件

cat[] = '推荐'
cat[] = '动漫'
cat[] = '游戏'
cat[] = '音乐'
cat[] = '科技'

获取导航的方式

public function navigation()
{
    $data = \Yaconf::get('navigation.cat');
    return $this->writeJson(0,'ok',$data);
}

我反正成功了!截图就不贴了.但是我做完感觉有点点瑕疵.我觉得应该加上下标,从1开始可能会比较好点吧.日后用着不行我就偷着改.不告诉你们 QQ20190430-0 下午11.43.46.gif 返回页面数据.需要获取传参

$parm = $this->request()->getRequestParam();

对于瀑布流这种下拉加载数据的形式.我们需要获取到当前的页数page.每次返回的数量size.推荐栏目cat_id.来看看我牛逼不得了的test表

create table `test`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY key,
    `name` varchar(100) NOT NULL DEFAULT '',
    `content` text NOT NULL,
    `create_time` int(10) unsigned NOT NULL DEFAULT '0',
  `update_time` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

其中我偷偷的已经加了26条数据.要获取到这些数据.遵循mvc设计思想.我们在App下创建Model目录.进入目录创建Video.php。如果看了EasySwoole推荐的Mysqli类库的话.那么你可能已经有了VideoModel.而且也知道里面其中的相关用法.我直接去掉我的测试方法.

namespace App\Model;

use App\Model\Model;
use EasySwoole\Component\Di;

class Video
{
    protected $db;
    protected $tableName = 'test';

    public function __construct()
    {
        $db = Di::getInstance()->get("MYSQL");
        if ($db instanceof \MysqliDb) {
            $this->db = $db;
        } else {
            $this->db = new \MysqliDb(\Yaconf::get('mysql'));
        }
    }
}

我们需要用到分页的方法查看MysqliDb.php

public $pageLimit = 20;
...
public function paginate ($table, $page, $fields = null) {
    $offset = $this->pageLimit * ($page - 1);
    $res = $this->withTotalCount()->get ($table, Array ($offset, $this->pageLimit), $fields);
    $this->totalPages = ceil($this->totalCount / $this->pageLimit);
    return $res;
}

可以看到这里需要三个传参$table表名,$page当前页数,$fields,要获取字段的名字.$this->pageLimit 在类里面做个默认配置是20.回到VideoModel做测试

/*
 * 获取视频数据
 * $condition 条件,默认为空
 * $page 当前页数,默认为1
 * $size 返回数量,默认为1
 * */
public function getVideoData($condition=[],$page=1,$size=10)
{
    $data = $this->db->arrayBuilder()->paginate($this->tableName,$page);
    echo $this->db->getLastQuery();
}

在VideoController中测调用IndexModel中的getVideoData

use App\Model\Video as VideoModel;
...
public function list()
{
    $parm = $this->request()->getRequestParam();
    $model = new VideoModel();
    $data = $model->getVideoData();
    print_r($data);
}

在终端打印结果为

SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 0, 20

测试查看第二页

$data = $model->getVideoData([],2);

在终端打印结果为

SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 20, 20

可以看到他默认的是20条.我们可以直接修改源码中的成员属性

public $pageLimit = 20;

但是如果我在另一个地方需要获取一百个呢?直接在这源码中写死也没办法通用所以的啊.这里我们就在Model层中进行修改

public function getVideoData($condition=[],$page=1,$size=10)
{
    if(!empty($size)){
        $this->db->pageLimit = $size;
    }
    $data = $this->db->arrayBuilder()->paginate($this->tableName,$page);
    echo $this->db->getLastQuery();
}

测试终端显示

SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 10, 10

这样我们就对数据可控制了～

在打印的sql中SQL_CALC_FOUND_ROWS 相当于count(*)。但是你在执行的时候发现SQL_CALC_FOUND_ROWS 并没有啊.还是和SELECT *没什么区别的呀

接下下来使用select found_rows();

相当于查询满足上一条SQL中符合条件的有几条数据.对于我们首页展示的数据.不光需要数据.还要知道当前的页数,一共有多少页.我们看刚才的源码中有这么一行.

$this->totalPages = ceil($this->totalCount / $this->pageLimit);

这就是总共的数量除以每页显示的数量向上取整得到总共的页数.所以我们直接调用就可以了修改VideoModel

public function getVideoData($condition = [], $page = 1, $size = 10)
{
    if (!empty($size)) {
        $this->db->pageLimit = $size;
    }
    $data = $this->db->arrayBuilder()->paginate($this->tableName, $page);
    // echo $this->db->getLastQuery();
    return [
        'totalPage' => $this->db->totalPages,
        'pageSize' => $this->db->pageLimit,
        'totalCount' => $this->db->totalCount,
        'lists' => $data
    ];
}

好了就直接在控制器中调用就行了.写代码一定要严谨哟.不要嫌麻烦,如果我们不像自己写code码这里可以去看一下easyswoole\http\src\Message\Status.php给我们封装好了一堆状态码.由于我的test表里忘了加cat_id.所以我这里先用content字段当一下栏目id哈.我已经懒到这种程度了吗,加个字段都跟要命一样.
修改控制器

use EasySwoole\Http\Message\Status;
...
public function list()
{
    $parm = $this->request()->getRequestParam();
    $condition = [];
    $page = !empty($parm['page']) ? intval($parm['page']) : 1;
    $size = !empty($parm['size']) ? intval($parm['size']) : 5;
    if (!empty($parm['content'])) {
        $condition['content'] = $parm['content'];
    }
    try {
        $model = new VideoModel();
        $data = $model->getVideoData($condition, $page, $size);
    } catch (\Exception $e) {
        return $this->writeJson(Status::CODE_BAD_REQUEST, $e->getMessage());
    }
    return $this->writeJson(Status::CODE_OK, 'ok', $data);
}

查出来是不错滴.但是好像不符合常理.我们默认应该让最新的显示出来.所以这他排个序.我们还是来看下源码

   /**
    * This method allows you to specify multiple (method chaining optional) ORDER BY statements for SQL queries.
    *
    * @uses $MySqliDb->orderBy('id', 'desc')->orderBy('name', 'desc', '^[a-z]')->orderBy('name', 'desc');
    *
    * @param string $orderByField         The name of the database field.
    * @param string $orderbyDirection
    * @param mixed  $customFieldsOrRegExp Array with fieldset for ORDER BY FIELD() ordering or string with regular expression for ORDER BY REGEXP ordering
    *
    * @return MysqliDb
    * @throws Exception
    */
   public function orderBy($orderByField, $orderbyDirection = "DESC", $customFieldsOrRegExp = null)
   {
       $allowedDirection = Array("ASC", "DESC");
       $orderbyDirection = strtoupper(trim($orderbyDirection));
       $orderByField = preg_replace("/[^ -a-z0-9\.\(\),_`\*\'\"]+/i", '', $orderByField);

       // Add table prefix to orderByField if needed.
       //FIXME: We are adding prefix only if table is enclosed into `` to distinguish aliases
       // from table names
       $orderByField = preg_replace('/(\`)([`a-zA-Z0-9_]*\.)/', '\1' . self::$prefix . '\2', $orderByField);


       if (empty($orderbyDirection) || !in_array($orderbyDirection, $allowedDirection)) {
           throw new Exception('Wrong order direction: ' . $orderbyDirection);
       }

       if (is_array($customFieldsOrRegExp)) {
           foreach ($customFieldsOrRegExp as $key => $value) {
               $customFieldsOrRegExp[$key] = preg_replace("/[^\x80-\xff-a-z0-9\.\(\),_` ]+/i", '', $value);
           }
           $orderByField = 'FIELD (' . $orderByField . ', "' . implode('","', $customFieldsOrRegExp) . '")';
       }elseif(is_string($customFieldsOrRegExp)){
    $orderByField = $orderByField . " REGEXP '" . $customFieldsOrRegExp . "'";
}elseif($customFieldsOrRegExp !== null){
    throw new Exception('Wrong custom field or Regular Expression: ' . $customFieldsOrRegExp);
}

       $this->_orderBy[$orderByField] = $orderbyDirection;
       return $this;
   }

。。。。好长.算了看看注视吧,英文好的看得懂注视就不需要去读源码,这也是能力强的好处！并不是因为我嫌麻烦.如果你给我钱我当然愿意解读源码
修改Model添加

$this->db->orderBy('id','desc');

为了让初级者看了不晕.我还是把改后的代码贴出来吧.这里也添加了栏目id的where条件.请一定要谢谢我这么认真的写文哦～

public function getVideoData($condition = [], $page = 1, $size = 10)
{
    if (!empty($size)) {
        $this->db->pageLimit = $size;
    }
    if (!empty($condition['content'])) {
        $this->db->where('content', $condition['content']);
    }
    $this->db->orderBy('id', 'desc');
    $data = $this->db->arrayBuilder()->paginate($this->tableName, $page);
    // echo $this->db->getLastQuery();
    return [
        'totalPage' => $this->db->totalPages,
        'pageSize' => $this->db->pageLimit,
        'totalCount' => $this->db->totalCount,
        'lists' => $data
    ];
}

测试一切都正常但是totalCount返回的是字符串格式的.回到Model给他都进行一下转换

return [
    'totalPage' => intval($this->db->totalPages),
    'pageSize' => intval($this->db->pageLimit),
    'totalCount' => intval($this->db->totalCount),
    'lists' => $data
];

先去吹会牛逼.日后再来优化它,bye

Last modification：February 18th, 2020 at 10:31 pm

EasySwoole开发首页——Mysql方案

羡仙. • 2019 年 05 月 09 日

<?php

namespace App\HttpController\Api;

use App\HttpController\Api\Base;

class Index extends Base
{
    public function navigation()
    {
        $data = \Yaconf::get('navigation');
        $data = $data['cat'];
        return $this->writeJson(0,'ok',$data);
    }
}

cat[] = '推荐'
cat[] = '动漫'
cat[] = '游戏'
cat[] = '音乐'
cat[] = '科技'

获取导航的方式

public function navigation()
{
    $data = \Yaconf::get('navigation.cat');
    return $this->writeJson(0,'ok',$data);
}

$parm = $this->request()->getRequestParam();

对于瀑布流这种下拉加载数据的形式.我们需要获取到当前的页数page.每次返回的数量size.推荐栏目cat_id.来看看我牛逼不得了的test表

create table `test`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY key,
    `name` varchar(100) NOT NULL DEFAULT '',
    `content` text NOT NULL,
    `create_time` int(10) unsigned NOT NULL DEFAULT '0',
  `update_time` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

namespace App\Model;

use App\Model\Model;
use EasySwoole\Component\Di;

class Video
{
    protected $db;
    protected $tableName = 'test';

    public function __construct()
    {
        $db = Di::getInstance()->get("MYSQL");
        if ($db instanceof \MysqliDb) {
            $this->db = $db;
        } else {
            $this->db = new \MysqliDb(\Yaconf::get('mysql'));
        }
    }
}

我们需要用到分页的方法查看MysqliDb.php

public $pageLimit = 20;
...
public function paginate ($table, $page, $fields = null) {
    $offset = $this->pageLimit * ($page - 1);
    $res = $this->withTotalCount()->get ($table, Array ($offset, $this->pageLimit), $fields);
    $this->totalPages = ceil($this->totalCount / $this->pageLimit);
    return $res;
}

可以看到这里需要三个传参$table表名,$page当前页数,$fields,要获取字段的名字.$this->pageLimit 在类里面做个默认配置是20.回到VideoModel做测试

/*
 * 获取视频数据
 * $condition 条件,默认为空
 * $page 当前页数,默认为1
 * $size 返回数量,默认为1
 * */
public function getVideoData($condition=[],$page=1,$size=10)
{
    $data = $this->db->arrayBuilder()->paginate($this->tableName,$page);
    echo $this->db->getLastQuery();
}

在VideoController中测调用IndexModel中的getVideoData

use App\Model\Video as VideoModel;
...
public function list()
{
    $parm = $this->request()->getRequestParam();
    $model = new VideoModel();
    $data = $model->getVideoData();
    print_r($data);
}

在终端打印结果为

SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 0, 20

测试查看第二页

$data = $model->getVideoData([],2);

在终端打印结果为

SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 20, 20

可以看到他默认的是20条.我们可以直接修改源码中的成员属性

public $pageLimit = 20;

但是如果我在另一个地方需要获取一百个呢?直接在这源码中写死也没办法通用所以的啊.这里我们就在Model层中进行修改

public function getVideoData($condition=[],$page=1,$size=10)
{
    if(!empty($size)){
        $this->db->pageLimit = $size;
    }
    $data = $this->db->arrayBuilder()->paginate($this->tableName,$page);
    echo $this->db->getLastQuery();
}

测试终端显示

SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 10, 10

这样我们就对数据可控制了～

在打印的sql中SQL_CALC_FOUND_ROWS 相当于count(*)。但是你在执行的时候发现SQL_CALC_FOUND_ROWS 并没有啊.还是和SELECT *没什么区别的呀

接下下来使用select found_rows();

$this->totalPages = ceil($this->totalCount / $this->pageLimit);

这就是总共的数量除以每页显示的数量向上取整得到总共的页数.所以我们直接调用就可以了修改VideoModel

public function getVideoData($condition = [], $page = 1, $size = 10)
{
    if (!empty($size)) {
        $this->db->pageLimit = $size;
    }
    $data = $this->db->arrayBuilder()->paginate($this->tableName, $page);
    // echo $this->db->getLastQuery();
    return [
        'totalPage' => $this->db->totalPages,
        'pageSize' => $this->db->pageLimit,
        'totalCount' => $this->db->totalCount,
        'lists' => $data
    ];
}

use EasySwoole\Http\Message\Status;
...
public function list()
{
    $parm = $this->request()->getRequestParam();
    $condition = [];
    $page = !empty($parm['page']) ? intval($parm['page']) : 1;
    $size = !empty($parm['size']) ? intval($parm['size']) : 5;
    if (!empty($parm['content'])) {
        $condition['content'] = $parm['content'];
    }
    try {
        $model = new VideoModel();
        $data = $model->getVideoData($condition, $page, $size);
    } catch (\Exception $e) {
        return $this->writeJson(Status::CODE_BAD_REQUEST, $e->getMessage());
    }
    return $this->writeJson(Status::CODE_OK, 'ok', $data);
}

查出来是不错滴.但是好像不符合常理.我们默认应该让最新的显示出来.所以这他排个序.我们还是来看下源码

   /**
    * This method allows you to specify multiple (method chaining optional) ORDER BY statements for SQL queries.
    *
    * @uses $MySqliDb->orderBy('id', 'desc')->orderBy('name', 'desc', '^[a-z]')->orderBy('name', 'desc');
    *
    * @param string $orderByField         The name of the database field.
    * @param string $orderbyDirection
    * @param mixed  $customFieldsOrRegExp Array with fieldset for ORDER BY FIELD() ordering or string with regular expression for ORDER BY REGEXP ordering
    *
    * @return MysqliDb
    * @throws Exception
    */
   public function orderBy($orderByField, $orderbyDirection = "DESC", $customFieldsOrRegExp = null)
   {
       $allowedDirection = Array("ASC", "DESC");
       $orderbyDirection = strtoupper(trim($orderbyDirection));
       $orderByField = preg_replace("/[^ -a-z0-9\.\(\),_`\*\'\"]+/i", '', $orderByField);

       // Add table prefix to orderByField if needed.
       //FIXME: We are adding prefix only if table is enclosed into `` to distinguish aliases
       // from table names
       $orderByField = preg_replace('/(\`)([`a-zA-Z0-9_]*\.)/', '\1' . self::$prefix . '\2', $orderByField);


       if (empty($orderbyDirection) || !in_array($orderbyDirection, $allowedDirection)) {
           throw new Exception('Wrong order direction: ' . $orderbyDirection);
       }

       if (is_array($customFieldsOrRegExp)) {
           foreach ($customFieldsOrRegExp as $key => $value) {
               $customFieldsOrRegExp[$key] = preg_replace("/[^\x80-\xff-a-z0-9\.\(\),_` ]+/i", '', $value);
           }
           $orderByField = 'FIELD (' . $orderByField . ', "' . implode('","', $customFieldsOrRegExp) . '")';
       }elseif(is_string($customFieldsOrRegExp)){
    $orderByField = $orderByField . " REGEXP '" . $customFieldsOrRegExp . "'";
}elseif($customFieldsOrRegExp !== null){
    throw new Exception('Wrong custom field or Regular Expression: ' . $customFieldsOrRegExp);
}

       $this->_orderBy[$orderByField] = $orderbyDirection;
       return $this;
   }

$this->db->orderBy('id','desc');

为了让初级者看了不晕.我还是把改后的代码贴出来吧.这里也添加了栏目id的where条件.请一定要谢谢我这么认真的写文哦～

public function getVideoData($condition = [], $page = 1, $size = 10)
{
    if (!empty($size)) {
        $this->db->pageLimit = $size;
    }
    if (!empty($condition['content'])) {
        $this->db->where('content', $condition['content']);
    }
    $this->db->orderBy('id', 'desc');
    $data = $this->db->arrayBuilder()->paginate($this->tableName, $page);
    // echo $this->db->getLastQuery();
    return [
        'totalPage' => $this->db->totalPages,
        'pageSize' => $this->db->pageLimit,
        'totalCount' => $this->db->totalCount,
        'lists' => $data
    ];
}

测试一切都正常但是totalCount返回的是字符串格式的.回到Model给他都进行一下转换

return [
    'totalPage' => intval($this->db->totalPages),
    'pageSize' => intval($this->db->pageLimit),
    'totalCount' => intval($this->db->totalCount),
    'lists' => $data
];

先去吹会牛逼.日后再来优化它,bye

EasySwoole开发首页——Mysql方案

Leave a Comment Cancel reply

ElasticSearch索引简单使用

使用LogStash插件将Mysql数据同步到ElasticSearch

MySQL高可用——MaxScale

ELK——日志可视化(Linux版)

ELK——日志可视化(Mac版)

EasySwoole使用Mysqli

Mac虚拟机安装CentOs7

ElasticSearch文档增删改

ElasticSearch安装-Mac/Linux

将本地文件上传至阿里云

EasySwoole开发首页——Mysql方案