laravel + schedule + mysql实现表结构自动分区示例

Laravel  /  管理员 发布于 2年前   629

分区表场景就不说了，有兴趣的自行搜索，下面就以laravel的platform_operation_log操作日志表为例，进行分区测试

1.分区表结构

CREATE TABLE `platform_operation_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL COMMENT '角色id',
  `ip` char(15) NOT NULL DEFAULT '0' COMMENT '注册ip',
  `method` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '请求分时',
  `username` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '操作人',
  `uri` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '请求地址',
  `parameter` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '请求参数',
  `response` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '响应内容',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `module` varchar(255) NOT NULL COMMENT '模块名称',
  PRIMARY KEY (`id`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=646 DEFAULT CHARSET=utf8 COMMENT='操作日志'

/*!50100 PARTITION BY RANGE (unix_timestamp(`created_at`))
(PARTITION p20220306 VALUES LESS THAN (1646496000) ENGINE = InnoDB,
 PARTITION p20220406 VALUES LESS THAN (1649174400) ENGINE = InnoDB,
 PARTITION p20220506 VALUES LESS THAN (1651766400) ENGINE = InnoDB,
 PARTITION p20220606 VALUES LESS THAN (1654444800) ENGINE = InnoDB,
 PARTITION p20220706 VALUES LESS THAN (1657036800) ENGINE = InnoDB,
 PARTITION p20220806 VALUES LESS THAN (1659715200) ENGINE = InnoDB,
 PARTITION p20220906 VALUES LESS THAN (1662393600) ENGINE = InnoDB,
 PARTITION p20221006 VALUES LESS THAN (1664985600) ENGINE = InnoDB) */;

2.需要运行的schedule脚本

<?php

namespace App\Console\Commands;

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Console\Command;

class SyncCheckPartition extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'sync:partition';
    
    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '每个月去检查Mysql表分区是否过期';
    
    /**
     * 所有分区的数据
     * 
     * @var int
     * @author ChenGuangHui
     * @dateTime 2022-10-21
     */
    protected $table_partition = [
        'platform_operation_log'
    ];
    
    /**
     * 执行脚本
     *
     * @return mixed|void
     * @throws \Exception
     */
    public function process()
    {
        $tables = DB::table('information_schema.partitions')
            ->selectRaw("any_value(partition_name) partition_name, any_value(partition_description) partition_description, any_value(table_name) table_name")
            ->whereIn('table_name', $this->table_partition)
            ->whereRaw('table_schema = SCHEMA()')
            ->whereRaw('partition_description in (select max(partition_description) from information_schema.partitions group by table_name)')
            ->orderBy('table_name')
            ->get()
            ->keyBy('table_name')
            ->toArray();
        // 获取需要分区的表
        foreach($tables as $table => $data){
            // 分区时间 在15天左右时间过期的
            if ($data->partition_description <= strtotime("+15 days")) {
                $dataTime = strtotime("+1 month", $data->partition_description);
                $dataDate = Carbon::createFromTimeStamp($dataTime, 'Asia/Shanghai')->format('Ymd');
                // 添加分区
                DB::select('ALTER TABLE ' . $table . ' ADD PARTITION (PARTITION `p' . $dataDate . '` VALUES LESS THAN (' . $dataTime . ') ENGINE = InnoDB)');
            }
        }
    }
}

运行就不写了，自行去laravel手册查看任务调度章节。