PHP分表查询sql

CI框架
/**
 * 公共函数库
 *
 * @author owen <2021-05-13 16:13>
 */

if (!function_exists('ajaxReturn')) {
    /**
     * Ajax方式返回数据到客户端
     * @access protected
     * @param mixed $code 要返回的数据
     * @param mixed $msg 要返回的消息
     * @param mixed $result 要返回的数据
     * @param int $rows 总条数
     * @param int $page_size 约定每页显示的信息条数
     * @param String $type AJAX返回数据格式
     * @param int $json_option 传递给json_encode的option参数
     * @return void
     */

    function ajaxReturn($code, $msg, $result = '', $rows = '', $page_size = '', $type = '', $json_option = 0)
    {
        $data = array(
            'code' => $code,
            'msg' => $msg
        );
        if (!empty($rows) && !empty($page_size)) {
            $data['total_rows'] = $rows;
            $data['total_page'] = ceil($rows / $page_size);//总页数
        }
        $data['data'] = $result;

        if (empty($type)) $type = 'JSON';
        switch (strtoupper($type)) {
            case 'JSON' :
                // 返回JSON数据格式到客户端 包含状态信息
                header('Content-Type:application/json; charset=utf-8');
                $data = json_encode($data, $json_option);
                break;
            case 'JSONP':
                // 返回JSON数据格式到客户端 包含状态信息
                header('Content-Type:application/json; charset=utf-8');
                $handler = isset($_GET['callback']) ? $_GET['callback'] : 'jsonpReturn';
                $data = $handler . '(' . json_encode($data, $json_option) . ');';
                break;
        }
        exit($data);
    }

}

/**
 * 获取分表数据
 *
 * @param string $table_name 要查询的表名字    必须
 * @param date $start_date 查询的起始时间    必须
 * @param date $end_date 查询的结束时间
 * @param int $start_row 查询的其实行
 * @param int $page_size 分页结束
 * @param string $where 查询条件
 * @param string $field 显示的字段
 * @param string $depot 库名
 *
 * @return string 分表查询字符串
 * Date: 2021/7/27 Time: 16:26
 * @author owen <邮箱>
 */
public function get_branch_table_sql($table_name,$place_id,$start_date, $end_date, $time_where,$start_row, $page_size, $field = "*", $depot = "mtt_report")
{
    #A.1 参数校验
    ($depot == "") && ajaxReturn(10001, "库名不能为空！");
    ($field == "") && ajaxReturn(10002, "指定列明不能为空！");
    ($start_date == "") && ajaxReturn(10003, "请选择起始时间！");
    ($end_date == "") && ajaxReturn(10004, "请选择结束时间！");
    (strtotime($end_date) > time()) && ajaxReturn(10005, "结束日期超出范围！");
    (strtotime($end_date) < strtotime($start_date)) && ajaxReturn(10006, "结束时间小于起始时间！");
    #($end_date == "") && $end_date = date('Y-m-d H:i:s', time());
    $depot = $depot . "_" . date("Y", time());
    #B.1 查询SQL 组合
    $month_begin = date('Ym', strtotime($start_date));
    $month_end = date('Ym', strtotime($end_date));
    $month_plus = 1;
    $month_next = date('Ym', strtotime("+{$month_plus} months", strtotime($start_date)));

    $where="";
    (!empty($place_id)||!empty($time_where))&&$where=" where 1=1 ";
    (!empty($place_id))&&$place_id=  " and place_id=".$place_id;
    !empty($time_where)&&$time_where=" and ".$time_where." >= ".strtotime($start_date)." and ".$time_where." <= ".strtotime($end_date);

    $UNION_SQL = "SELECT {$field} FROM " . $depot . "." . $table_name . "_" . $month_begin.$where.$place_id. $time_where;
    #起始表校验
    $check_depot_table_result = $this->check_depot_table($depot, $table_name . "_" . $month_next, $month_next);
    if (true !== $check_depot_table_result) {
        $check_mark = false;
        ajaxReturn("10001", "请检索库：" . $depot . "、表:" . $table_name . "_" . $month_begin . "的分表是否存在!");
    }
    $check_mark = true; #标识检索库表是否通过
    #遍历month组合分表
    while ((intval($month_next) <= intval($month_end))) {
        #B.2  未找到表  则跳出循环
        if (true !== $this->check_depot_table($depot, $table_name . "_" . $month_next, $month_next)) {
            $check_mark = false;
            $check_mark_array = array("10001", "请检索库：" . $depot . "、表:" . $table_name . "_" . $month_next . "的分表是否存在,");
            break;
        }
        $UNION_SQL .= " UNION ALL SELECT {$field} FROM " . $depot . "." . $table_name . "_" . $month_next.$where.$place_id. $time_where;
        $month_plus += 1;
        $month_next = date('Ym', strtotime("+{$month_plus} months", strtotime($start_date)));
    }
    !$check_mark && ajaxReturn($check_mark_array[0], $check_mark_array[1]);
    return   ajaxReturn(10000,"ok",$UNION_SQL);
    #C.1  完成SQL 查询
    #$sql = "SELECT * FROM ($UNION_SQL) branch_table   limit " . (int)$start_row . ", " . (int)$page_size;


    #return $sql;
}

/**
 * 检查库表是否存在
 *
 * @param string $depot 库名
 * @param string $table_name 表名
 * @param string $mark 年月标识
 * @return bool 返回说明
 * Date: 2021/7/29 Time: 17:32
 * @author owen <邮箱>
 */
public function check_depot_table($depot, $table_name, $mark)
{
    if ($depot == "") return false;# && ajaxReturn(10001, "没有指定库名");
    if ($table_name == "") return false;#&& ajaxReturn(10002, "没有指定表名");
    if ($mark == "") return false;#&& ajaxReturn(10003, "没有指定年月标识");
    #查库
    $query_depot = $this->db->get_where('information_schema.SCHEMATA ', array('SCHEMA_NAME' => $depot));
    $row_depot = $query_depot->row();
    if (!isset ($row_depot)) return false; #ajaxReturn(10004, "没有找到库:" . $depot);
    #查表
    $query_table = $this->db->get_where('information_schema.TABLES  ', array('TABLE_NAME ' => $table_name));
    $row_table = $query_table->row();
    //exit($this->db->last_query());
    if (!isset ($row_table)) return false; #ajaxReturn(10005, "没有找到表:" . $table_name);
    #分表检索
    $query = $this->db->get_where('mtt_report.cp_branch_table_record',
        array(
            'is_branch' => 1,
            'depot' => $depot,
            'table_name' => $table_name,
            'mark' => $mark));
    $row = $query->row();
    if (isset ($row)) return true;
    #ajaxReturn(10000, "OK:" . $table_name);
    return false;
    #ajaxReturn(10006, "请确认分表是否已经生成、分表信息是否已经记录！:" . $table_name);
}

/**
 * 检测ID表是否存在
 *
 * @param 参数数据类型 参数变量 参数说明
 *
 * @return 返回数据类型 返回说明
 * Date: 2021/7/27 Time: 11:03
 * @author owen <邮箱>
 */
public function table_exists($table_name = "", $depot = "mtt_report")
{
    $table_namea = $table_name . "_model";
    #A.1  检测ID表是否存在
    $table_exists_sql = "SELECT
                         count(*) as table_num
                        FROM
                         information_schema.TABLES t 
                        WHERE
                         t.TABLE_SCHEMA = '$depot' 
                     AND t.TABLE_NAME = '$table_namea'";
    $query = $this->db->query($table_exists_sql);
    $res = $query->row();
    #A.2 表存不存在则创建
    if ($res->table_num < 1) {
        $created_table = "CREATE TABLE IF NOT EXISTS mtt_report.`$table_namea`  (
                       `id` bigint(20) NOT NULL AUTO_INCREMENT,
                        PRIMARY KEY (`id`)
                        ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='{$table_name}ID表';";
        $created = $this->db->query($created_table);
        $this->db->insert($depot . '.cp_branch_table_record', array('table_name' => $table_name));
    }
}

/**
 * 生成分表
 *
 * @param 参数数据类型 参数变量 参数说明
 *
 * @return 返回数据类型 返回说明
 * Date: 2021/7/30 Time: 11:03
 * @author owen <邮箱>
 */
public function created_branch_table($depot, $talble)
{
    $start_date = date('Y-') . "01-01 00:00:00";
    for ($i = 0; $i < 12; $i++) {
        $month_next = date('Ym', strtotime("+{$i} months", strtotime($start_date)));
        $sql = "CREATE  TABLE IF NOT EXISTS " . $depot . "." . $talble . "_" . $month_next . "  LIKE mtt_report.{$talble}";
        #echo $sql."</br>";
        $query = $this->db->query($sql);
        $sql = " INSERT INTO `mtt_report`.`cp_branch_table_record` (`depot`, `table_name`, `mark`, `model`, `is_branch`) 
                    VALUES ('" . $depot . "', '" . $talble . "_" . $month_next . "' , $month_next, '" . $talble . "', 1)";
        $query = $this->db->query($sql);
    }
}

/**
 * 获取某表的ID
 *
 * @param string $table_name 获取ID的表名
 *
 * @return 返回数据类型 返回说明
 * Date: 2021/7/26 Time: 16:35
 * @author owen <邮箱>
 */


public function get_table_id($table_name, $data = array(), $depot = "mtt_report")
{
    #A.1  检测表是否已经存在记录
    $query = $this->db->where('table_name', $table_name . "_model")
        ->get($depot . '.cp_branch_table_record');
    if (!$query->num_rows()) #没有找到生成记录
        $this->table_exists($table_name);  #则生成对应ID表

    #B.1插入对应表记录返回ID
    $query = $this->db->set($data)
        ->insert($depot . "." . $table_name . '_model');   #."_".date('n')
    log_message("debug", $this->db->last_query());
    return $query ? $this->db->insert_id() : false;
}