您好,登錄后才能下訂單哦!
本文主要給大家介紹mysql數(shù)據(jù)批量刪除并且整理表碎片方法,文章內(nèi)容都是筆者用心摘選和編輯的,具有一定的針對(duì)性,對(duì)大家的參考意義還是比較大的,下面跟筆者一起了解下mysql數(shù)據(jù)批量刪除并且整理表碎片方法吧。
DROP PROCEDURE IF EXISTS prc_del_loop;
CREATE PROCEDURE prc_del_loop()
BEGIN
DECLARE v_count INT ;
DECLARE v_data_free INT;
my_del_loop:LOOP
delete from t_data where adress = '蘇州日志分析蘇州日志分析' limit 10000;
select count(1) into v_count from (
select * from t_data where adress = '蘇州日志分析蘇州日志分析' limit 1 ) d;
IF v_count=0 THEN
LEAVE my_del_loop;
END IF;
END LOOP my_del_loop;
SELECT "TABLE DATA HAD DELETED !!!";
#整理碎片,data_free大于等于100M進(jìn)行整理
select round(sum(data_free/1024/1024),0) into v_data_free
from information_schema.tables where TABLE_SCHEMA= 'data' and table_name = 't';
IF v_data_free >=100 THEN
SELECT "TABLE SHRINK IS RUNNING!!!";
alter table t ENGINE=INNODB;
SELECT "TABLE SHRINK IS FINISHED!!!";
ELSE
SELECT "TABLE SHRINK IS NOT NEED SHRINK!!!";
END IF;
END;
使用游標(biāo)來(lái)進(jìn)行刪除:
drop PROCEDURE if EXISTS prc_del_loop_cur;
CREATE PROCEDURE prc_del_loop_cur()
BEGIN
DECLARE v_name VARCHAR(2000);
DECLARE v_id BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT id,name from test where adress = '蘇州日志分析蘇州日志分析';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
open cur1;
my_del_loop:LOOP
fetch cur1 into v_id,v_name;
IF done=1 THEN
LEAVE my_del_loop;
END IF;
delete from test where id = v_id;
END LOOP my_del_loop;
CLOSE cur1;
SELECT "delete over !!!";
END;
call prc_del_loop_cur;
看完以上關(guān)于mysql數(shù)據(jù)批量刪除并且整理表碎片方法,很多讀者朋友肯定多少有一定的了解,如需獲取更多的行業(yè)知識(shí)信息 ,可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。