3.1、资源过期处理操作说明

资源过期处理操作说明

下载安装 ossutil

https://help.aliyun.com/zh/oss/developer-reference/install-ossutil?spm=a2c4g.11186623.help-menu-31815.d_3_3_2_0.4a3348a2k78wnm

生成oss通迁移

使用 SQL 查询要清理的数据

-- 查询学年学期
select * from ty_admin.jc_xndm jx  where jx.xxdm  = '8286' and jx.dqxnxq = 1;

-- 查询要清理的文件
select count(1)
  from pj_file pf
 where pf.school_code = '8286'
   and pf.create_time < '2024-07-01 00:00:00'
   and pf.url like concat('%oss-cn-hangzhou.aliyuncs.com/', school_code, '/evaluation/%');

select count(1)
  from pj_file pf
 where pf.school_code = '8286'
   and pf.create_time < '2024-07-01 00:00:00'
   and pf.mime_type like 'image%'
   and pf.url like concat('%oss-cn-hangzhou.aliyuncs.com/', school_code, '/evaluation/%');

select count(1)
  from pj_file pf
 where pf.school_code = '8286'
   and pf.create_time < '2024-07-01 00:00:00'
   and pf.mime_type like 'video%'
   and pf.url like concat('%oss-cn-hangzhou.aliyuncs.com/', school_code, '/evaluation/%');

select count(1)
  from pj_file pf
 where pf.school_code = '8286'
   and pf.create_time < '2024-07-01 00:00:00'
   and pf.mime_type like 'audio%'
   and pf.url like concat('%oss-cn-hangzhou.aliyuncs.com/', school_code, '/evaluation/%');

使用SQL脚步，对要清理的资源进行地址备份和设置过期展示内容

-- 备份原资源访问地址
update pj_file pf
   set pf.url_bak = pf.url
 where pf.url_bak is null
   and pf.school_code = '8286';

-- 将待清理图片文件设置过期效果
update pj_file pf
   set pf.`path` = '/image-expire.png',
       pf.`url` = 'https://91sst.oss-cn-hangzhou.aliyuncs.com/image-expire.png'
 where pf.school_code = '8286'
   and pf.create_time < '2024-07-01 00:00:00'
   and pf.mime_type like 'image%'
   and pf.url like concat('%oss-cn-hangzhou.aliyuncs.com/', school_code, '/evaluation/%');

-- 将待清理音频文件设置过期效果
update pj_file pf
   set pf.`path` = '/audio-expire.mp3' ,
       pf.`url` = 'https://91sst.oss-cn-hangzhou.aliyuncs.com/audio-expire.mp3'
 where pf.school_code = '8286'
   and pf.create_time < '2024-07-01 00:00:00'
   and pf.mime_type like 'audio%'
   and pf.url like concat('%oss-cn-hangzhou.aliyuncs.com/', school_code, '/evaluation/%');

-- 将待视频图片文件设置过期效果
update pj_file pf
   set pf.`path` = '/video-expire.mp4' ,
       pf.`url` = 'https://91sst.oss-cn-hangzhou.aliyuncs.com/video-expire.mp4'
 where pf.school_code = '8286'
   and pf.create_time < '2024-07-01 00:00:00'
   and pf.mime_type like 'video%'
   and pf.url like concat('%oss-cn-hangzhou.aliyuncs.com/', school_code, '/evaluation/%');

-- 备份原资源记录
  insert into `ty_evaluation_bak`.pj_file_202412
select * from `ty_evaluation`.pj_file pf
 where pf.school_code = '128'
   and pf.create_time < '2024-07-01 00:00:00';

使用 SQL 查询数据，拼接出 ossutil 备份命令清单

-- 生成 ossutil 备份命令
select concat("ossutil cp oss://",replace(SUBSTRING_INDEX(pf.url, '//', -1), '.oss-cn-hangzhou.aliyuncs.com/','/'),
       " oss://tyback", replace(replace(SUBSTRING_INDEX(url, '//', -1), '91sst.oss-cn-hangzhou.aliyuncs.com/', '/20241215/'), SUBSTRING_INDEX(url, '/', -1),'')) as cmd
  from pj_file pf
 where pf.school_code ='8286'
   and pf.create_time < '2024-07-01 00:00:00'
   and pf.url like concat('%oss-cn-hangzhou.aliyuncs.com/', pf.school_code, '/evaluation/%')
 order by pf.id desc;

使用 SQL 查询数据，拼接出 ossutil 删除命令清单

-- 生成 ossutil 删除命令
select concat("ossutil rm ",replace(replace(replace(url,'https://','oss://'),'http://','oss://'),'.oss-cn-hangzhou.aliyuncs.com/','/'),"")
  from pj_file pf
 where pf.school_code = '8286'
   and pf.create_time < '2024-07-01 00:00:00'
   and pf.url like concat('%oss-cn-hangzhou.aliyuncs.com/',school_code,'/evaluation/%')
 order by id desc;

把生成的内容，复制到 E:\commands.txt

创建 shell.bat ，内容如下

@echo off
setlocal enabledelayedexpansion

:: 设置OSSUtil的路径
set OSSUTIL_PATH=%BASE%

:: 设置命令文件的路径
set COMMAND_FILE=E:\commands.txt

:: 初始化变量
set ERROR_COUNT=0

:: 循环读取文件中的每一行
for /f "delims=" %%i in (%COMMAND_FILE%) do (
    set CMD_LINE=%%i
    echo Executing: !CMD_LINE!

    :: 执行ossutil命令
    %OSSUTIL_PATH% !CMD_LINE! --exclude "*.mp4" --exclude "*.mov" --exclude "*.MP4" --exclude "*.MOV" --exclude "*.mp3" -r -f

    :: 检查命令是否执行成功
    if !ERRORLEVEL! neq 0 (
        set /a ERROR_COUNT+=1
        echo Command failed: !CMD_LINE!
    )
)

:: 输出错误总数
echo.
echo Total errors: %ERROR_COUNT%

endlocal

执行 shell.bat