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); }