如何实现清除Mysql数据库的特定表无用数据的存储过程

如何实现清除Mysql数据库的特定表无用数据的存储过程，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

第一次玩存储过程，遇到好多问题，记录一下供日后参考。

说明：其中 logtable 是一个临时表用来调试用的，有"index,log1，log2，log3，log4，log5，log6，log7，log8"九个字段。

CREATE TABLE IF NOT EXISTS logtable(
`index` double NOT NULL AUTO_INCREMENT,
`log1` varchar(255) DEFAULT NULL,
`log2` varchar(255) DEFAULT NULL,
`log3` varchar(255) DEFAULT NULL,
`log4` varchar(255) DEFAULT NULL,
`log5` varchar(255) DEFAULT NULL,
`log6` varchar(255) DEFAULT NULL,
`log7` varchar(255) DEFAULT NULL,
PRIMARY KEY (`index`)
);

#定义存储过程

#定义函数

DROP PROCEDURE IF EXISTS insertAsSelectProducts;

CREATE PROCEDURE insertAsSelectProducts ()

BEGIN
DECLARE tablesNameVar VARCHAR (100);
DECLARE done INT DEFAULT 0;
DECLARE datatime VARCHAR (15) DEFAULT '201910010000';
DECLARE columnVar VARCHAR (100) DEFAULT '0';
DECLARE loginfo VARCHAR (100) DEFAULT '0';
#SET datatime ='201910010000';
#如果不存在日志表则创建

#查出要清理数据的特定表
DECLARE tableName CURSOR FOR SELECT table_name FROM information_schema.TABLES
WHERE table_schema = '26片区'
AND table_name NOT LIKE '%设定表'
# AND table_name LIKE '%日统计%';
AND table_name NOT LIKE '%实时%'
AND table_name <> 'logtable'
AND table_name NOT LIKE '%巡检%';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

#打开游标
OPEN tableName;

group_loop :LOOP
FETCH tableName INTO tablesNameVar;
IF done = 1 THEN
#日志
INSERT INTO logtable(log1) SELECT 'done==1 so leave loop';
LEAVE group_loop;
END IF;

#拼接sql 查询需要的数据复制到×_copy表
#日志
INSERT INTO logtable(log1) SELECT tablesNameVar;

SET @columnflag = 0;
#判断该表中是否有datatime列
SET @columnVar1 = 0;
SET @var_sql = concat(
'SELECT count(*) into @columnVar1 ',
" FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = '",
tablesNameVar,
"' AND column_name = 'datatime'"
);

PREPARE s FROM @var_sql;
EXECUTE s;
DEALLOCATE PREPARE s;

IF @columnVar1<>1 THEN
#判断该表中是否有'统计日期'列
SET @columnVar1 = 0;
SET @var_sql = concat(
'SELECT count(*) into @columnVar1 ',
" FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = '",
tablesNameVar,
"' AND column_name = '统计日期'"
);

PREPARE s FROM @var_sql;
EXECUTE s;
DEALLOCATE PREPARE s;

IF @columnVar1=1 THEN
SET @columnflag = 2;
ELSE
SET @columnflag = 3;
END IF;
ELSE
SET @columnflag = 1;
END IF;

#日志
UPDATE logtable SET log5 = @columnVar1 WHERE log1 = tablesNameVar;

IF @columnflag=1 THEN
SET @var_sql1 = concat(
'CREATE TABLE ',
tablesNameVar,
'_COPY ',
'SELECT * FROM ',
tablesNameVar,
' WHERE DATATIME > ',
datatime
);

SET @var_sql1_count = concat(
'SELECT count(*) into @columnVar1_count FROM ',
tablesNameVar,
' WHERE DATATIME > ',
datatime
);
ELSEIF @columnflag=2 THEN
SET @var_sql1 = concat(
'CREATE TABLE ',
tablesNameVar,
'_COPY ',
'SELECT * FROM ',
tablesNameVar,
' WHERE 统计日期 > ',
LEFT(datatime,8)
);

SET @var_sql1_count = concat(
'SELECT count(*) into @columnVar1_count FROM ',
tablesNameVar,
' WHERE 统计日期 > ',
LEFT(datatime,8)
);
ELSE
SET @var_sql1 = concat(
'CREATE TABLE ',
tablesNameVar,
'_COPY ',
'SELECT * FROM ',
tablesNameVar,
' WHERE 日期 > ',
LEFT(datatime,8)
);

SET @var_sql1_count = concat(
'SELECT count(*) into @columnVar1_count FROM ',
tablesNameVar,
' WHERE 日期 > ',
LEFT(datatime,8)
);
END IF;

#判断×_copy 表是否存在，存在就删除
SET @sql_Exit = concat(
'drop table if exists ',
tablesNameVar,
'_COPY '
);
PREPARE s1_exit FROM @sql_Exit;
EXECUTE s1_exit;
DEALLOCATE PREPARE s1_exit;

PREPARE s1_count FROM @var_sql1_count;
EXECUTE s1_count;
DEALLOCATE PREPARE s1_count;

UPDATE logtable SET log7 = @columnVar1_count WHERE log1 = tablesNameVar;

#判断该表是否有要备份的数据，没有要备份的数据直接清空该表，进入下一个循环，
IF @columnVar1_count<1 THEN
SET @clear_sql = concat(
'TRUNCATE TABLE ',
tablesNameVar
);
PREPARE s_clear FROM @clear_sql;
EXECUTE s_clear;
DEALLOCATE PREPARE s_clear;
#日志
UPDATE logtable SET log7 = CONCAT(@columnVar1_count,': no userful data,so continue next loop') WHERE log1 = tablesNameVar;
ITERATE group_loop;
END IF;

#把要保留的数据拷贝到新表 ×_copy 中
PREPARE s1 FROM @var_sql1;
EXECUTE s1;
DEALLOCATE PREPARE s1;
#日志
SET loginfo = CONCAT('Copy useful data from ',tablesNameVar,' successful!');
UPDATE logtable SET log2 = loginfo WHERE log1 = tablesNameVar;

#删除原有表
SET @var_sql2 = concat(
'DROP TABLE ',
tablesNameVar
);

PREPARE s2 FROM @var_sql2;
EXECUTE s2;
DEALLOCATE PREPARE s2;
#日志
SET loginfo = CONCAT('drop the old table：',tablesNameVar);
UPDATE logtable SET log3 = loginfo WHERE log1 = tablesNameVar;

#把×_copy表重命名为原表
SET @var_sql3 = concat(
'RENAME TABLE ',
tablesNameVar,
'_COPY ',
'TO ',
tablesNameVar
);

PREPARE s3 FROM @var_sql3;
EXECUTE s3;
DEALLOCATE PREPARE s3;
#日志
SET loginfo = CONCAT('rename the copy table：',tablesNameVar,'_copy to original name');
UPDATE logtable SET log4 = loginfo WHERE log1 = tablesNameVar;

END LOOP group_loop;

CLOSE tableName;

END

其中遇到的坑：

1.当在游标循环过程中select的结果集如果为空，就会触发游标continue handler for not found 提前将done置为1，导致提前退出游标。
2.游标循环中出现 select into 赋值 为 null 的时候，循环也会提前退出。

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。