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