最近因为需要统计大量的分表数据,传统操作非常麻烦,而且数据不容易掌握和维护,在这里总结一套方法。
原理:先将所有分表满足的结果查询出来,用 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); //分页