存储过程清理数据/删除表/重命名表-阿里云开发者社区

存储过程清理数据/删除表/重命名表

2016-05-12 2132

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介：

在做开发的过程中，会往数据库里写入很多测试的垃圾数据，到数据库需要正式发布的时候，这些测试数据必须清理掉。前面有同事用一条条delete 命令，组合成一个SQL文件去执行，很冗长，也很繁琐。于是思考能否做成一个通用的存储过程，只需要传入需要清理的数据库名称，然后自动清除所有的测试数据呢？晚上找时间写了如下的存储过程，在MYSQL5.1.42版本测试通过。

   Sql代码  
    
  
CREATE PROCEDURE Clear_Table_Data(  
    DB_NAME varchar(50) # 数据库名称  
)  
BEGIN  
  DECLARE done INT DEFAULT 0; #游标的标志位  
  DECLARE a varchar(260);  
  DECLARE b varchar(300);  
  DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME ;  
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  
   
  OPEN cur1;  
  REPEAT  
    FETCH cur1 INTO a;  
    IF NOT done THEN  
       set b=concat('Delete from ',DB_NAME,'.',a); # 拼删除命令  
       # set b=concat('TRUNCATE from ',DB_NAME,'.',a); # 拼删除命令  
       SET @E=b;  
       PREPARE stmt1 FROM @E;  
          EXECUTE stmt1; # 执行命令  
          DEALLOCATE PREPARE stmt1; #释放对象  
    END IF;  
  UNTIL done END REPEAT;  
  CLOSE cur1;  
END;  

call Clear_Table_Data('wms_site_ks03');

   Java代码  
    
  
CREATE PROCEDURE Drop_Db_Table(  
    DB_NAME varchar(50),   # 数据库名称             
    TB_PREFIX varchar(150) # 表前缀  
)  
BEGIN  
  DECLARE done INT DEFAULT 0; #游标的标志位  
  DECLARE a varchar(260);  
  DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME AND table_name LIKE CONCAT(TB_PREFIX, '%');  
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  
   
  OPEN cur1;  
  REPEAT  
    FETCH cur1 INTO a;  
    IF NOT done THEN  
       SET @E=concat('DROP TABLE ',DB_NAME,'.',a); # 拼删除命令  
       PREPARE stmt1 FROM @E;  
          EXECUTE stmt1; # 执行命令  
          DEALLOCATE PREPARE stmt1; #释放对象  
    END IF;  
  UNTIL done END REPEAT;  
  CLOSE cur1;  
END;  

MySQL批量删除指定前缀表

   Java代码  
    
  
#SHOW TABLES LIKE 'dede_%'  
Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE 'dede_%'  INTO  
OUTFILE '/tmp/a.txt';  
SOURCE /tmp/a.txt;  

另外一个就是批量修改表名：

   Java代码  
    
Select CONCAT( 'ALTER TABLE ', table_name, ' RENAME TO ', table_name,';' ) FROM information_schema.tables Where table_name LIKE 'dede_%';