ThinkPHP8 按月分表,合并表查询

使用 Db::table() 方法和 union 方法来实现按月分表的合并查询

按月分表的数据表结构，例如 data_202311、data_202312、data_202401等

1.创建数据的时候判断当月表是否存在，有则不创建表直接写入数据，没有则创建并写入数据

    /**
     * 创建记录日志
     * @param int $uid
     * @param array $param
     * @return mixed
     */
    public function create(int $uid, array $param)
    {
        //判断表是否存在//ba_front_log_202312
        $table = env('database.prefix', 'ba_') . "front_log_" . date('Ym', time());
        $check = Db::query("show tables like '{$table}'");
        if (empty($check)) {
            $sql = $this->getCreateSql($table);
            Db::execute($sql);
        }
        return Db::table($table)->insert(array_merge(['uid' => $uid], $param));
    }

    /**
     * 获取创建表sql
     * @param $table
     * @return string
     */
    protected function getCreateSql($table)
    {
        return <<<EOT
CREATE TABLE `{$table}`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '名称',
  `uid` int(11) NULL DEFAULT 0 COMMENT '用户uid',
  `room_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '房间号',
  `order_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '订单号',
  `data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '数据',
  `client_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '客户端ID',
  `client_type` tinyint(4) NULL DEFAULT 0 COMMENT '客户端类型:0=未知,1=H5,2=微信小程序,3=APP',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '用户名',
  `create_time` int(10) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` int(10) NULL DEFAULT NULL COMMENT '更新时间',
  `delete_time` int(10) NULL DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '前端-日志' ROW_FORMAT = Dynamic;
EOT;
    }

2.查询数据

    /**
     * 获取前端记录日志
     * @param $param
     * @return mixed
     */
    public function index($param)
    {
        $where = [];
        if (!empty($param['name']) && $param['name']) {
            $where[] = ['name', 'like', "%{$param['name']}%"];
        }

        if (!empty($param['order_id']) && $param['order_id']) {
            $where[] = ['order_id', 'like', "%{$param['order_id']}%"];
        }

        if (!empty($param['uid']) && $param['uid']) {
            $where[] = ['uid', '=', $param['uid']];
        }

        if (empty($where)) {
            return [];
        }

        $table = [];
        //查询所有表
        for ($year = 2023; $year <= date('Y'); $year++) {
            for ($month = 1; $month <= 12; $month++) {
                $tableName = env('database.prefix', 'ba_') . 'front_log_' . $year . sprintf('%02d', $month);
                $check     = Db::query("show tables like '{$tableName}'");
                if ($check) {
                    $table[] = 'SELECT * FROM ' . $tableName;
                }
            }
        }

        return Db::name('front_log')
            ->union(function ($query) use ($tableName, $where) {
                $query->table($tableName)
                    ->where($where);
            })
            ->where($where)
            ->page($param['page'], $param['limit'])
            ->select();
    }