189 8069 5689
如何实现清除MySQL数据库的特定表无用数据的存储过程，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
广德网站制作公司哪家好，找创新互联公司！从网页设计、网站建设、微信开发、APP开发、响应式网站开发等网站项目制作，到程序开发，运营维护。创新互联公司自2013年创立以来到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联公司。
第一次玩存储过程，遇到好多问题，记录一下供日后参考。
说明：其中 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 的时候，循环也会提前退出。
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注创新互联行业资讯频道，感谢您对创新互联的支持。