PHP使用，按时间水平分表，跨月多表条件查询数据分页显示

1.创建测试表并添加一些对应数据

sh_user_visit_202405 uid,create_time
sh_user_visit_202406 uid,create_time
sh_user_visit_202407 uid,create_time

2.格式化表

//获取表数组
public function getListByCross($table_prefix, $start_date, $end_date){
    if($end_date > date("Y-m-d H:i:s", time())){
        $end_date = date("Y-m-d H:i:s", time());
    }
    $startY = date('Y',strtotime($start_date)); //开始时间所在年份
    $startm = date('m',strtotime($start_date));  //开始时间的开始月份
    $endY = date('Y',strtotime($end_date));  //结束时间所在年份
    $endm = date('m',strtotime($end_date));  //结束时间的开始月份
    $ym_arr = [];//获取跨年月数组
    if($startY==$endY){ //不跨年
        for($i=$startm;$i<=$endm;$i++){
            $ym_arr[] = $startY.str_pad($i,2,'0',STR_PAD_LEFT);
        }
    }
    else{ //跨年跨月
        $dostartY = $startY;
        do{
            if($startY==$dostartY){
                for($i=$startm;$i<=12;$i++){
                    $ym_arr[] = $startY.str_pad($i,2,'0',STR_PAD_LEFT);
                }
            }elseif($startY < $endY){
                for($i=0;$i<=12;$i++){
                    $ym_arr[] = $startY.str_pad($i,2,'0',STR_PAD_LEFT);
                }
            }else{
                for($i=1;$i<=$endm;$i++){
                    $ym_arr[] = $startY.str_pad($i,2,'0',STR_PAD_LEFT);
                }
            }
            $startY++;
        }while($startY<=$endY);
    }
    $tables = [];
    foreach($ym_arr as $val){
        $tables[] = $table_prefix . $val;
    }
    return $tables;
}

3.调用

public function test(){
    $params = $this->request->param();
    if(!isset($params['start_date']) && !isset($params['end_date'])){
        return $this->success("ok",[]);
    }
   // 分页设置
   $page = $params['page'] ?? 1; // 当前页码
    $limit = $params['limit'] ?? 15; // 每页显示的记录数
    $offset = ($page - 1) * $limit; // 计算偏移量
    // 初始化相关变量
    $table_prefix = "sh_user_visit_";
    $start_date = $params['start_date']; //查询开始时间
    $end_date = $params['end_date']; //查询结束时间

    $start = $table_prefix.date('Ym',strtotime($start_date));
    $isTable = Db::query("SHOW TABLES LIKE '{$start}'");
    if (!$isTable) {
        return $this->error("日期错误");
    }
    //获取表数组
    $tables = $this->getListByCross($table_prefix, $start_date, $end_date);
    //条件查询
    $where_str = "`create_time` BETWEEN '".$start_date."' AND '".$end_date."'";
    if(isset($params['uid']) && $params['uid'] != ''){
        $where_str .= 'AND uid = '. $params['uid'];
    }
    $union_query = '';
    foreach ($tables as $table) {
        if ($union_query != "") {
            $union_query .= " UNION ALL ";
        }
        $union_query .= "SELECT * FROM {$table} WHERE ". $where_str;
    }
    // 获取总记录数
    $total_query = "SELECT COUNT(*) AS total FROM ({$union_query}) AS unioned_tables";
    $result = Db::query($total_query);
    $count = $result[0]['total'];
    // 分页查询
    $paged_query = "{$union_query} LIMIT $limit OFFSET $offset";
    $result = Db::query($paged_query);
    $data = [
        'page' => $page,
        'count' => $count,
        'data' => $result
    ];
    return $this->success("ok",$data);
}