webman的分表操作

laravel的orm操作

namespace App\model;
use support\Db;
use support\Model;
class DemoModel extends Model
{
    protected $table="demo";
    public function __construct(array $attributes = [])
        {
            $data_table = $this->getTable();
            $this->table = $data_table.date('Ym');
            $result = Db::schema()->hasTable($this->table);
            if (empty($result)) {
                Db::update('create table '  . $this->table . ' like ' .   $data_table);
            }
            parent::__construct($attributes);
        }
}

thinkphp的orm操作方法

namespace App\model;
use think\facade\Db;
use think\Model;
class DemoModel extends Model
{
    protected $table="demo";
    public function __construct(array $attributes = [])
        {
            $data_table = $this->getTable();
            $this->table = $data_table.date('Ym');
            $prefix = config('thinkorm.prefix')??'';
            $result = Db::query("SHOW TABLES LIKE '{$prefix}{$this->table}'");
            if (empty($result)) {
                Db::query('create table '  . $this->table . ' like ' .   $data_table);
            }
            parent::__construct($attributes);
        }
}

/**
 * 获取分表数据
 * @param string $table_name 原始表名
 * @param array $where 条件
 * @param int $page 分页
 * @param int $page_er 页数数量
 * @param array $select 表字段
 * @param array $orderBy 排序
 * @return mixed
 */
function getSubTableData(string $table_name = "", $where = [], int $page = 0, int $page_er = 10, array $orderBy = ['id'=>'desc'],$select = ['*'])
{
    $offset = $page * $page_er;
    // 开始日期
    $start = Carbon::parse(date('Y-01-01'));
    // 结束日期
    $end = Carbon::now();
    // 查询集合
    $queries = collect();
    //把最原始表单的数据加入
    if(Schema::hasTable($table_name)){
        $queries->push(
            DB::table($table_name)
                // 建议都用select查询字段，SQL尽可能的优化性能
                ->select($select)
                ->where($where)
        );
    }
    // 循环比较年月，添加每一张表的查询
    for ($i = $start->copy(); $i->format('Y-m') <= $end->format('Y-m'); $i->addMonth()) {

        // 按日期循环组装出表名
        $tableName = $table_name . "_{$i->format('Ym')}";
        // 检查这个表是否存在
        if (Schema::hasTable($tableName)) {
            $queries->push(
                DB::table($tableName)
                    // 建议都用select查询字段，SQL尽可能的优化性能
                    ->select($select)
                    ->where($where)
            );
        }
    }
    $unionQuery = $queries->shift();
    // 循环剩下的表添加union
    $queries->each(function ($item, $key) use ($unionQuery) {
        $unionQuery->unionAll($item);
    });
    // 把用 unionAll链接起来的sql 组成一个表
    $model = DB::table($table_name)
        // 添加临时表
        ->from(DB::raw("({$unionQuery->toSql()}) AS {$table_name}"))
        // 合并查询条件
        ->mergeBindings($unionQuery);
    if ($orderBy) {
        foreach ($orderBy as $k=>$v){
            // 按时间倒序
            $model->orderBy($k, $v);
        }
    }
    $data['total'] = $model->count();
    $data['data'] = $model->limit($page_er)->offset($offset)->get()->toArray();
    return $data;
}