关于tp5 水平分表分页查询解决方案

关于tp5 水平分表分页查询解决方案

1、我们首先创建数据表tb_article

CREATE TABLE `tb_article` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stub` char(1) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2、创建字表tb_article_1、tb_article_2、tb_article_3

CREATE TABLE `tb_article_1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章id',
  `user_id` int(11) NOT NULL COMMENT '作者id',
  `add_time` int(10) DEFAULT NULL COMMENT '时间',
  `typeid` int(1) DEFAULT NULL COMMENT '文章栏目 1财经 2娱乐 3体育',
  `title` varchar(155) NOT NULL COMMENT '文章标题',
  `content` text NOT NULL COMMENT '文章内容',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='文章表1';

3、创建model

<?php
namespace app\index\model;
use think\Model;

class Article extends Model
{
    protected $table = 'tb_article';
    private function getRule(){
        return [
            'type' => 'mod', // 分表方式
            'num'  => 3     // 分表数量
        ];
    }    
    //获取插入ID，根据ID来取模分表
    public function getLastId(){
        $data['stub']='a';
        return $this->insertGetId($data, true);
    }
	//添加数据
    public function saveData($data, $id){
        return $this->partition(['id' => $id], "id", $this->getRule())->insert($data);
    }

	//获取一条数据
    public function getArticleById($where, $field = "*", $id){
        return $this->partition(['id' => $id], "id", $this->getRule())->where($where)->field($field)->select();
    }

	//修改数据
    public function upArticleById($where, $id){
        return $this->partition(['id' => $id], "id", $this->getRule())->where($where)->update(['user_id'=>12345879]);
    }

    /**

     * 构造获取总记录数及主键ID的sql子查询语句

     * @param $table 主表名称

     * @param $idKey 主键id字段名称

     * @param string $fields 其它字段名称,多个字段用英文逗号分隔

     * @param int $num 子表数量
     * @param string $where 查询条件
     * @return array

     */

    function buildPartitionSql($table,$idKey,$where='',$fields='',$num=3) {

        $countTable = [];

        $listTable = [];

        $fieldList = [$idKey];

        if ($fields) {

            $fieldList = array_merge($fieldList,explode(',',$fields));

            $fieldList = array_unique($fieldList);

        }

        $fieldStr = implode(',',$fieldList);
        if(empty($where)){
            $where = "1=1";
        }

        for ($i = 0; $i < $num; $i++) {

            $countTable[] = sprintf('SELECT %s FROM %s_%s where %s', $idKey, $table, ($i + 1), $where);

            $listTable[] = sprintf('SELECT %s FROM %s_%s where %s', $fieldStr,$table, ($i + 1), $where);

        }

        $countTable = implode(" UNION ", $countTable);

        $listTable = implode(" UNION ", $listTable).' order by id desc limit 10';

        $tables = ['countSql' => $countTable, 'listSql' => $listTable];

        return $tables;

    }
}

4、其次创建控制器

<?php
namespace app\index\controller;use think\Controller;
use app\index\model\Article as ArticleModel;
use think\Db;

class Article extends Controller
{
    //添加数据
    public function articleAdd(){
        $Article = new ArticleModel();
        $id = $Article->getLastId();
        $data = [
            'id' => $id,
            'user_id' => 10,
            'add_time' => time(),
            'typeid' => 3,
            'title' => '这里是新闻标题',
            'content' => '这里是新闻内容',
        ];
        if($Article->saveData($data,$id)){
            $this->success('文章添加成功！');
        }
    }
    //查询一条数据
    public function getArticle(){
        $id = input('id');
        if($id){
            $Article = new ArticleModel();
            $news = $Article->getArticleById(['id'=>$id],'*',$id);
            return json($news);
        }
    }

	//排序分页
    public function getArticles(){
        $id = input('id');
        if($id){
            $Article = new ArticleModel();
            $news = $Article->buildPartitionSql('tb_article','id',"",'id,user_id,add_time,typeid,title,content');
            $list = Db::query($news['listSql']);
            return json($list);
        }
    }
}