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

超、凢脫俗 2023-09-24 10:22 17阅读 0赞

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

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

\#定义存储过程

DROP PROCEDURE IF EXISTS ClearUnusefulData;

CREATE PROCEDURE ClearUnusefulData()

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='联网'

AND table\_name NOT LIKE '%设定表'

\# AND table\_name LIKE '%日统计%';

AND table\_name NOT LIKE '%实时%'

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;

\#判断该表中是否有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;

\#日志

UPDATE logtable SET log5=@columnVar1 WHERE log1=tablesNameVar;

IF @columnVar1=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

);

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;

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 的时候，循环也会提前退出。

来源：

链接：

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关循环删除mysql数据库表中已标识为删除的数据的存储过程

相关 MySql数据库存储过程

相关 mysql 清除表中的数据（TRUNCATE ）

相关 mysql清除表数据

相关清除mysql表中数据

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

相关 mysql 清理大量数据的存储过程_MySQL-存储过程-高效清理数据

相关 mysql 动态分表_MySQL动态创建表，数据分表的存储过程

相关 MySQL数据库存储过程

相关 MySQL数据库存储过程