實現MySQL表結構自動分割槽指令碼

SammlHui發表於2022-12-07
MySql指令碼

分割槽表結構

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) */;

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)');
            }
        }
    }
}
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章