mysql 動態新建以及刪除分割槽表
drop procedure if exists general_procedure;
-- general_procedure的作用:新建分割槽表及在table_fen_qu表中儲存新建分割槽表時的相關引數
-- general_procedure的引數:表名,分割槽表之間的時間間隔(單位為小時),要新增的分割槽表個數
create procedure general_procedure(in tablenamein varchar(50),in intervalHour int,in newIntervalNum int)
general_pro:begin
-- 引數:最大時間
declare maxMonitTime datetime default SYSDATE();
-- 引數:最大時間對應的字串
declare maxMonitTimeStr varchar(50);
-- 引數:最小時間
declare minMonitTime datetime default SYSDATE();
-- 引數:最大時間對應的字串
declare minMonitTimeStr varchar(50);
-- 引數:資料庫記錄數
declare recoidNum int default 0;
-- 判斷傳入的表名是否為空
if tablenamein is null then
leave general_pro;
end if;
-- 判斷傳入的時間間隔
if intervalHour <= 0 then
set intervalHour = 6;
end if;
-- 判斷新增分割槽表個數
if newIntervalNum <= 0 then
set newIntervalNum = 1;
end if;
-- 在該表中,查詢符合條件的記錄數,backupflag=0說明是未備份
select count(*) into recoidNum from table_fen_qu where tablename=tablenamein and backupflag=0;
if recoidNum > 0 then
-- 查詢該表在table_fen_qu表中的最大監測時間
select monittime into maxMonitTime from table_fen_qu where tablename=tablenamein and backupflag=0 order by monittime desc limit 1;
-- 判斷監測時間是否為null
if maxMonitTime is null then
set maxMonitTime = SYSDATE();
end if;
-- 比較最大時間減去72個小時之後的時間與系統時間的早晚
set recoidNum = timestampdiff(hour,SYSDATE(),DATE_SUB(maxMonitTime,INTERVAL 3 DAY));
-- 如果recoidNum大於0,說明最大監測時間減去72小時之後的時間仍然在系統時間之後,
-- 說明不用建立新的分割槽表,反之,則建立最大監測時間之後newIntervalNum個以每intervalHour小時為間隔的分割槽表
if recoidNum <= 0 then
set recoidNum = 1;
while recoidNum <= newIntervalNum do
set maxMonitTime = ADDDATE(maxMonitTime,INTERVAL intervalHour HOUR);
set maxMonitTimeStr = CONCAT('p',DATE_FORMAT(maxMonitTime,"%Y%m%d%H%i%s"));
-- 開始新增分割槽表
-- 拼接分割槽表程式碼段
set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' ADD PARTITION (PARTITION ',maxMonitTimeStr,' VALUES LESS THAN (\'',maxMonitTime,'\') ENGINE = InnoDB )');
-- 定義預處理語句
prepare stmt from @v_add_s;
-- 執行預處理語句
execute stmt;
-- 釋放預處理語句
deallocate prepare stmt;
-- 開始在table_fen_qu中新增記錄
insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(maxMonitTimeStr,tablenamein,maxMonitTime,0);
-- 記錄數加1
set recoidNum = recoidNum + 1;
end while;
end if;
else
set recoidNum = 2;
-- 計算最小時間
set minMonitTimeStr = CONCAT(DATE_FORMAT(DATE_SUB(maxMonitTime,INTERVAL 60 DAY),'%Y-%m-%d'),' 00:00:00');
set minMonitTime = STR_TO_DATE(minMonitTimeStr,'%Y-%m-%d %H:%i:%s');
-- 計算最大時間
set maxMonitTimeStr = CONCAT(DATE_FORMAT(ADDDATE(maxMonitTime,INTERVAL 4 DAY),'%Y-%m-%d'),' 00:00:00');
set maxMonitTime = STR_TO_DATE(maxMonitTimeStr,'%Y-%m-%d %H:%i:%s');
-- 計算新建表分割槽個數
set newIntervalNum = floor(timestampdiff(hour,minMonitTime,maxMonitTime) / intervalHour) + 1;
if newIntervalNum < 12 then
set newIntervalNum = 12;
end if;
-- 刪除所有表分割槽
set @v_del_s = CONCAT('ALTER TABLE ',tablenamein,' remove partitioning');
-- 定義預處理語句
prepare stmt from @v_del_s;
-- 執行預處理語句
execute stmt;
-- 釋放預處理語句
deallocate prepare stmt;
-- 刪除所有資料
delete from table_fen_qu where tablename= tablenamein;
-- 新建分割槽
-- 設定時間
set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));
-- 拼接新增分割槽表sql
set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' PARTITION BY RANGE COLUMNS(moint_time) (PARTITION ',minMonitTimeStr,' VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB,');
-- 開始在table_fen_qu中新增記錄
insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);
while recoidNum <= newIntervalNum do
-- 設定時間
set minMonitTime = ADDDATE(minMonitTime,INTERVAL intervalHour HOUR);
set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));
-- 拼接新增分割槽表sql
set @v_add_s = CONCAT(@v_add_s,'PARTITION ',minMonitTimeStr,' VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB,');
-- 開始在table_fen_qu中新增記錄
insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);
-- 記錄數加1
set recoidNum = recoidNum + 1;
end while;
set @v_add_s = left(@v_add_s,LENGTH(@v_add_s)-1);
set @v_add_s = CONCAT(@v_add_s,');');
-- 定義預處理語句
prepare stmt from @v_add_s;
-- 執行預處理語句
execute stmt;
-- 釋放預處理語句
deallocate prepare stmt;
end if;
end general_pro;
-- general_procedure的作用:新建分割槽表及在table_fen_qu表中儲存新建分割槽表時的相關引數
-- general_procedure的引數:表名,分割槽表之間的時間間隔(單位為小時),要新增的分割槽表個數
create procedure general_procedure(in tablenamein varchar(50),in intervalHour int,in newIntervalNum int)
general_pro:begin
-- 引數:最大時間
declare maxMonitTime datetime default SYSDATE();
-- 引數:最大時間對應的字串
declare maxMonitTimeStr varchar(50);
-- 引數:最小時間
declare minMonitTime datetime default SYSDATE();
-- 引數:最大時間對應的字串
declare minMonitTimeStr varchar(50);
-- 引數:資料庫記錄數
declare recoidNum int default 0;
-- 判斷傳入的表名是否為空
if tablenamein is null then
leave general_pro;
end if;
-- 判斷傳入的時間間隔
if intervalHour <= 0 then
set intervalHour = 6;
end if;
-- 判斷新增分割槽表個數
if newIntervalNum <= 0 then
set newIntervalNum = 1;
end if;
-- 在該表中,查詢符合條件的記錄數,backupflag=0說明是未備份
select count(*) into recoidNum from table_fen_qu where tablename=tablenamein and backupflag=0;
if recoidNum > 0 then
-- 查詢該表在table_fen_qu表中的最大監測時間
select monittime into maxMonitTime from table_fen_qu where tablename=tablenamein and backupflag=0 order by monittime desc limit 1;
-- 判斷監測時間是否為null
if maxMonitTime is null then
set maxMonitTime = SYSDATE();
end if;
-- 比較最大時間減去72個小時之後的時間與系統時間的早晚
set recoidNum = timestampdiff(hour,SYSDATE(),DATE_SUB(maxMonitTime,INTERVAL 3 DAY));
-- 如果recoidNum大於0,說明最大監測時間減去72小時之後的時間仍然在系統時間之後,
-- 說明不用建立新的分割槽表,反之,則建立最大監測時間之後newIntervalNum個以每intervalHour小時為間隔的分割槽表
if recoidNum <= 0 then
set recoidNum = 1;
while recoidNum <= newIntervalNum do
set maxMonitTime = ADDDATE(maxMonitTime,INTERVAL intervalHour HOUR);
set maxMonitTimeStr = CONCAT('p',DATE_FORMAT(maxMonitTime,"%Y%m%d%H%i%s"));
-- 開始新增分割槽表
-- 拼接分割槽表程式碼段
set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' ADD PARTITION (PARTITION ',maxMonitTimeStr,' VALUES LESS THAN (\'',maxMonitTime,'\') ENGINE = InnoDB )');
-- 定義預處理語句
prepare stmt from @v_add_s;
-- 執行預處理語句
execute stmt;
-- 釋放預處理語句
deallocate prepare stmt;
-- 開始在table_fen_qu中新增記錄
insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(maxMonitTimeStr,tablenamein,maxMonitTime,0);
-- 記錄數加1
set recoidNum = recoidNum + 1;
end while;
end if;
else
set recoidNum = 2;
-- 計算最小時間
set minMonitTimeStr = CONCAT(DATE_FORMAT(DATE_SUB(maxMonitTime,INTERVAL 60 DAY),'%Y-%m-%d'),' 00:00:00');
set minMonitTime = STR_TO_DATE(minMonitTimeStr,'%Y-%m-%d %H:%i:%s');
-- 計算最大時間
set maxMonitTimeStr = CONCAT(DATE_FORMAT(ADDDATE(maxMonitTime,INTERVAL 4 DAY),'%Y-%m-%d'),' 00:00:00');
set maxMonitTime = STR_TO_DATE(maxMonitTimeStr,'%Y-%m-%d %H:%i:%s');
-- 計算新建表分割槽個數
set newIntervalNum = floor(timestampdiff(hour,minMonitTime,maxMonitTime) / intervalHour) + 1;
if newIntervalNum < 12 then
set newIntervalNum = 12;
end if;
-- 刪除所有表分割槽
set @v_del_s = CONCAT('ALTER TABLE ',tablenamein,' remove partitioning');
-- 定義預處理語句
prepare stmt from @v_del_s;
-- 執行預處理語句
execute stmt;
-- 釋放預處理語句
deallocate prepare stmt;
-- 刪除所有資料
delete from table_fen_qu where tablename= tablenamein;
-- 新建分割槽
-- 設定時間
set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));
-- 拼接新增分割槽表sql
set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' PARTITION BY RANGE COLUMNS(moint_time) (PARTITION ',minMonitTimeStr,' VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB,');
-- 開始在table_fen_qu中新增記錄
insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);
while recoidNum <= newIntervalNum do
-- 設定時間
set minMonitTime = ADDDATE(minMonitTime,INTERVAL intervalHour HOUR);
set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));
-- 拼接新增分割槽表sql
set @v_add_s = CONCAT(@v_add_s,'PARTITION ',minMonitTimeStr,' VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB,');
-- 開始在table_fen_qu中新增記錄
insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);
-- 記錄數加1
set recoidNum = recoidNum + 1;
end while;
set @v_add_s = left(@v_add_s,LENGTH(@v_add_s)-1);
set @v_add_s = CONCAT(@v_add_s,');');
-- 定義預處理語句
prepare stmt from @v_add_s;
-- 執行預處理語句
execute stmt;
-- 釋放預處理語句
deallocate prepare stmt;
end if;
end general_pro;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23490154/viewspace-1063384/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 【實驗】【PARTITION】RANGE分割槽表刪除分割槽
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- 對刪除分割槽的分割槽表執行TSPITR
- ORACLE刪除-表分割槽和資料Oracle
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- MySQL線上轉分割槽表(以及TiDB)MySqlTiDB
- SQL Server表分割槽刪除詳情DSCCSQLServer
- 資料表分割槽分割與刪除歷史資料
- 刪除LINUX分割槽Linux
- Linux 分割槽刪除Linux
- swap分割槽新增刪除
- 【MYSQL】 分割槽表MySql
- MySQL 分割槽表MySql
- MySQL分割槽表MySql
- ORACLE 刪除無用分割槽Oracle
- hive 動態分割槽插入資料表Hive
- Hive的靜態分割槽與動態分割槽Hive
- MySQL 分割槽表探索MySql
- mysql 分表 分割槽MySql
- mysql 分割槽表用法MySql
- MySQL表分割槽管理MySql
- win10 oem分割槽怎麼刪除_win10 oem分割槽可以刪除嗎Win10
- win10分割槽好了怎麼刪除_win10分割槽完後如何刪除Win10
- delete_partition.pl 刪除分割槽delete
- Hive動態分割槽Hive
- MySQL定期自動刪除表MySql
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- mysql 進行表分割槽MySql
- Mysql表分割槽實現MySql
- mysql分割槽表筆記MySql筆記
- MySQL入門--分割槽表MySql
- mysql —— 分表分割槽(1)MySql
- MySQL的List分割槽表MySql
- Hive中靜態分割槽和動態分割槽總結Hive
- Oracle定時刪除、增加表分割槽(TOAD,PL/SQL)(轉)OracleSQL
- oracle 分割槽表 概念以及常用操作Oracle