您好,登錄后才能下訂單哦!
這篇“MySQL如何實(shí)現(xiàn)清空分區(qū)表單個(gè)分區(qū)數(shù)據(jù)”文章的知識點(diǎn)大部分人都不太理解,所以小編給大家總結(jié)了以下內(nèi)容,內(nèi)容詳細(xì),步驟清晰,具有一定的借鑒價(jià)值,希望大家閱讀完這篇文章能有所收獲,下面我們一起來看看這篇“MySQL如何實(shí)現(xiàn)清空分區(qū)表單個(gè)分區(qū)數(shù)據(jù)”文章吧。
ALTER TABLE xxx TRUNCATE PARTITION p20220104;
功能:指定清空之前某一天的數(shù)據(jù),直接調(diào)用存儲過程實(shí)現(xiàn)
DELIMITER $$ USE `managerdb`$$ DROP PROCEDURE IF EXISTS `partition_trunc`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_trunc`(p_schema_name VARCHAR(64), p_table_name VARCHAR(64), p_trunc_before_date INT) BEGIN /* p_trunc_before_date 清空分區(qū)表第N天的數(shù)據(jù) */ DECLARE trunc_part_name VARCHAR(16); SET trunc_part_name = CONCAT('p',DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL p_trunc_before_date DAY),'%Y%m%d')); SET @trunc_partitions = CONCAT("ALTER TABLE ", p_schema_name, ".", p_table_name, " TRUNCATE PARTITION ",trunc_part_name); -- 拼執(zhí)行語句 SELECT @trunc_partitions; -- 打印刪除詳情 PREPARE STMT FROM @trunc_partitions; EXECUTE STMT; DEALLOCATE PREPARE STMT; END$$ DELIMITER ;
實(shí)例:
call managerdb.partition_trunc('test','t_001',1);
清空test.t_001一天前的單個(gè)分區(qū)數(shù)據(jù)
mysql分區(qū)表功能特別有用,其中一個(gè)應(yīng)用就是保存固定時(shí)間的數(shù)據(jù)信息,自動(dòng)分區(qū)自動(dòng)purge,不用擔(dān)心數(shù)據(jù)量越積累越多。
比較實(shí)用的一個(gè)實(shí)現(xiàn)方式是表一天一個(gè)分區(qū),保持固定天數(shù)的數(shù)據(jù)。
以數(shù)據(jù)庫log為例,里面有一個(gè)表tb_log, 按天分區(qū),始終保存最新的30天的數(shù)據(jù)。
存儲過程sp_create_log_partition和sp_drop_log_partition用于創(chuàng)建和刪除分區(qū)。
事件event_log_auto_partition每天執(zhí)行一次,用于向前創(chuàng)建新的分區(qū)和刪除過期的分區(qū)。
存儲過程和事件結(jié)合使用就實(shí)現(xiàn)了tb_log數(shù)據(jù)的自動(dòng)分區(qū)自動(dòng)刪除。
-- -- Definition for database log -- DROP DATABASE IF EXISTS log; CREATE DATABASE IF NOT EXISTS log CHARACTER SET utf8 COLLATE utf8_general_ci; -- -- Set default database -- USE log; -- -- Definition for table tb_log -- CREATE TABLE IF NOT EXISTS tb_log ( id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, log varchar(512) NOT NULL DEFAULT '', PRIMARY KEY (id, created_at) ) ENGINE = INNODB AUTO_INCREMENT = 1 AVG_ROW_LENGTH = 16384 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci PARTITION BY RANGE(TO_DAYS(created_at)) ( PARTITION pbasic VALUES LESS THAN (0) ); DELIMITER $$ -- -- Definition for procedure sp_create_log_partition -- CREATE DEFINER = 'uiadmin'@'%' PROCEDURE sp_create_log_partition (day_value datetime, tb_name varchar(128)) BEGIN DECLARE par_name varchar(32); DECLARE par_value varchar(32); DECLARE _err int(1); DECLARE par_exist int(1); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET _err = 1; START TRANSACTION; SET par_name = CONCAT('p', DATE_FORMAT(day_value, '%Y%m%d')); SELECT COUNT(1) INTO par_exist FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'log' AND TABLE_NAME = tb_name AND PARTITION_NAME = par_name; IF (par_exist = 0) THEN SET par_value = DATE_FORMAT(day_value, '%Y-%m-%d'); SET @alter_sql = CONCAT('alter table ', tb_name, ' add PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (TO_DAYS("', par_value, '")+1))'); PREPARE stmt1 FROM @alter_sql; EXECUTE stmt1; END IF; END $$ -- -- Definition for procedure sp_drop_log_partition -- CREATE DEFINER = 'uiadmin'@'%' PROCEDURE sp_drop_log_partition (day_value datetime, tb_name varchar(128)) BEGIN DECLARE str_day varchar(64); DECLARE _err int(1); DECLARE done int DEFAULT 0; DECLARE par_name varchar(64); DECLARE cur_partition_name CURSOR FOR SELECT partition_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'log' AND table_name = tb_name ORDER BY partition_ordinal_position; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET _err = 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SET str_day = DATE_FORMAT(day_value, '%Y%m%d'); OPEN cur_partition_name; REPEAT FETCH cur_partition_name INTO par_name; IF (str_day > SUBSTRING(par_name, 2)) THEN SET @alter_sql = CONCAT('alter table ', tb_name, ' drop PARTITION ', par_name); PREPARE stmt1 FROM @alter_sql; EXECUTE stmt1; END IF; UNTIL done END REPEAT; CLOSE cur_partition_name; END $$ -- -- Definition for event event_log_auto_partition -- CREATE DEFINER = 'uiadmin'@'%' EVENT event_log_auto_partition ON SCHEDULE EVERY '1' DAY STARTS '1972-01-01 00:00:00' ON COMPLETION PRESERVE DO BEGIN CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 3 DAY), 'tb_log'); CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 2 DAY), 'tb_log'); CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 1 DAY), 'tb_log'); CALL sp_create_log_partition(NOW(), 'tb_log'); CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 1 DAY), 'tb_log'); CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 2 DAY), 'tb_log'); CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 3 DAY), 'tb_log'); CALL sp_drop_log_partition(DATE_ADD(NOW(), INTERVAL - 30 DAY), 'tb_log'); END $$ -- -- Create partitions based on current time -- CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 3 DAY), 'tb_log')$$ CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 2 DAY), 'tb_log')$$ CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 1 DAY), 'tb_log')$$ CALL sp_create_log_partition(NOW(), 'tb_log')$$ CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 1 DAY), 'tb_log')$$ CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 2 DAY), 'tb_log')$$ CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 3 DAY), 'tb_log')$$ DELIMITER ;
select TABLE_SCHEMA, TABLE_NAME,PARTITION_NAME from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='tb_log';
在磁盤上一個(gè)分區(qū)表現(xiàn)為一個(gè)文件,所以刪除操作會很快完成的。
以上就是關(guān)于“MySQL如何實(shí)現(xiàn)清空分區(qū)表單個(gè)分區(qū)數(shù)據(jù)”這篇文章的內(nèi)容,相信大家都有了一定的了解,希望小編分享的內(nèi)容對大家有幫助,若想了解更多相關(guān)的知識內(nèi)容,請關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。