mysql数据库分表查询（基于laravel5.2）

最近因为需要统计大量的分表数据，传统操作非常麻烦，而且数据不容易掌握和维护，在这里总结一套方法。

原理：先将所有分表满足的结果查询出来，用 union all 将所有结果集形成一张表，最后操作这张‘表’。

基于laravel封装的类：

<?php
namespace App\Traits\Model;

use Carbon\Carbon;
use DB;

Trait UnionTableTrait
{
    /*
    * protected $splitTable = 'cdb_honey_log'; 原始表
    * protected $splitTableNameRule = '_Yn'; 分表名称规则
    * protected $splitTableMode = 'month'; 分表方式 year, month, day
    * const CREATED_AT; 数据的添加时间字段，最好用回laravel自带的
    * protected $dateFormat = 'U' 时间戳
    */

    public function __construct()
    {
        $this->table = $this->splitTable . Carbon::now()->format($this->splitTableNameRule);
    }

    public function setUnionAllTable($start = null, $end = null, $colums = '*', $where = '1')
    {


        // 开始日期,和日期有关的，都建议用内置的carbon处理，非常好用，而且简洁
        $start = is_numeric($start) ? Carbon::createFromTimestamp($start) : Carbon::parse($start);
        // 结束日期
        $end = is_numeric($end) ? Carbon::createFromTimestamp($end) : Carbon::parse($end);


        // 循环规则
        switch ($this->splitTableMode) {
            case 'year':
                $loopFormat = 'Y';
                $loopIncrement = 'addYear';
                $startOf = 'startOfYear';
                break;
            case 'month':
                $loopFormat = 'Y-m';
                $loopIncrement = 'addMonth';
                $startOf = 'startOfMonth';
                break;
            case 'day':
                $loopFormat = 'Y-m-d';
                $loopIncrement = 'addDay';
                $startOf = 'startOfDay';
                break;
        }

        $dateFormat = $this->dateFormat ? $this->dateFormat : 'Y-m-d H:i:s';
        $betweenDate = [$start->format($dateFormat), $end->format($dateFormat)];
        // 判断是否需要联合查询
        /*
        if ($start->format($loopFormat) == $end->format($loopFormat))

            return $this->setTable("{$this->splitTable}{$start->format($this->splitTableNameRule)}")
                ->selectRaw($colums)
                ->whereBetween(self::CREATED_AT, $betweenDate)
                ->whereRaw($where);
                */

        // 查询集合
        $queries = collect();
        // 循环比较，添加每一张表的查询
        for ($i = $start->copy()->$startOf(); $i->format($loopFormat) <= $end->format($loopFormat); $i->$loopIncrement()) {
           // dd($betweenDate);
            $queries->push(
                DB::table("{$this->splitTable}{$i->format($this->splitTableNameRule)}")
                    // 建议都用select查询字段，SQL尽可能的优化性能
                    ->selectRaw($colums)
                    ->whereBetween(self::CREATED_AT, $betweenDate)
                    ->whereRaw($where)
            );
        }

        // 出列一张表作为union的开始
        $unionQuery = $queries->shift();

        // 循环剩下的表添加union
        $queries->each(function ($item, $key) use ($unionQuery) {
            $unionQuery->unionAll($item);
        });
        return $this->setTable($this->splitTable)
            ->from(DB::raw("({$unionQuery->toSql()}) as {$this->splitTable}"))
            // 合并查询条件
            ->mergeBindings($unionQuery);
    }
}

分表的表名：a_user_call_2017_04 , 字段有：id 、target_uid 、channel_time

//分表的模型代码：
<?php
namespace App\Models;

use App\Traits\Model\UnionTableTrait;//引入上面的类

class UserCall extends BaseModel
{

    use UnionTableTrait;
    protected $connection = 'mysql';
    protected $splitTable = 'a_user_call'; 
    protected $splitTableNameRule = '_Y_m';   
    protected $splitTableMode = 'month'; 
    const CREATED_AT = 'channel_time';      // 分表依据的字段
    protected $dateFormat = 'U';

}
// 这个模型就具备连表的功能

例子：

//控制器中的代码

se Illuminate\Pagination\Paginator;
use DB;
use App\Models\UserCall;

// 查询分表的条件
$where1 = " channel_time>={$startTime} AND
            channel_time<{$endTime} AND
            chat_type={$chatType}
            ";
//查询字段
$select ="  target_uid,b.nickname,
            SUM(*) AS total_consume,
            COUNT(*) AS total_call ";

$lists1 = with(new UserCall)->setUnionAllTable($startTime, $endTime, ['*'], $where1)   // 这里查找分表的约束条件和字段，所满足的结果联合成一张表
            ->select(DB::raw($select)) //这里可以重新查找字段，注：selectRaw()方法会返回所有表字段
            ->leftJoin('b_beauty as b ','target_uid','=','b.id') //左联表
            ->whereRaw($where)     // 这里的条件不可再填分表的字段条件
            ->groupBy('target_uid')//分组
            ->paginate(15);        //分页