delete-drop语句生成的存储过程
问题：
开发时有时候需要对很多表进行操作。
例如：清空(删除)所有(某些)表，删除所有表某时间点之后导入的数据(类似于回滚)
解决方式：
对选定的表集合执行相同的查询条件(可为空)，如果这个执行结果大于阈值，则使用相同的条件组建delete语句。
delete_drop_sql生成器用法：
delete_drop_sql_generator
(var_where [where条件，可以为空,例如：" where LEFT(CREATE_time,19)>'2021-08-04'"]
,var_include_tbl_list [要包含的表名列表,优先于var_exclude_tbl_list,例如："tbl_name1,tbl_name2"]
,var_exclude_tbl_list [要排除的表名列表,仅在var_include_tbl_list为空时生效,例如："tbl_name1,tbl_name2"]
,var_greater_than_value [符合where条件要过滤的值,count(*)>=0 ]
)
1 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 2 DROP PROCEDURE IF EXISTS delete_drop_sql_generator; 3 DELIMITER %% 4 CREATE PROCEDURE delete_drop_sql_generator(var_where VARCHAR(2048),var_include_tbl_list VARCHAR(2048),var_exclude_tbl_list VARCHAR(2048),var_greater_than_value VARCHAR(100)) 5 label:BEGIN 6 7 /*------------每个表使用同样的过滤条件---------------------------------*/ 8 /* 9 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 10 适用场景： 11 1.将所有表2021-08-01日插入的记录全部删除。例如刚配置业务数据全部删除，只要确定某个时间段，只有你的数据在里面 12 13 2.将某些表相同字段的记录删除，自定义哪些表，必须同时含有where条件中的字段。 14 */ 15 DROP TABLE if exists temp_filter_table; 16 DROP TABLE if exists temp_var_query_table; 17 CREATE table temp_var_query_table(tbl_name VARCHAR(512)); 18 19 /*-----------------------------------------------------------------------------*/ 20 -- ----------配置项目----------- 21 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 22 /*-----------------------------------------------------------------------------*/ 23 SET @var_where = var_where; 24 -- "LEFT(create_time,19)>'2021-08-04'";-- 格式：2021-08-11 16:32:37.872 25 -- select @var_where; 26 27 -- 如果含有include，则已include为准。 28 if(var_include_tbl_list IS NULL OR var_include_tbl_list='include_tbl_list' OR var_include_tbl_list='' OR var_include_tbl_list=' ' OR var_include_tbl_list=' ') then 29 if(var_exclude_tbl_list IS NULL OR var_exclude_tbl_list='exclude_tbl_list' OR var_exclude_tbl_list='' OR var_exclude_tbl_list=' ' OR var_exclude_tbl_list=' ') then 30 -- 如果包含和不含字段都是为空，将库中所有的表加入进去。 31 INSERT INTO temp_var_query_table SELECT t.table_name FROM information_schema.tables t WHERE table_schema=DATABASE() AND t.TABLE_NAME !='temp_var_query_table'; 32 else 33 -- 如果include为空，但是exclude不为空 34 SET @exec_sql = CONCAT_WS('',"INSERT INTO temp_var_query_table SELECT t.table_name FROM information_schema.tables t WHERE table_schema=DATABASE() AND t.TABLE_NAME not in ('", REPLACE(var_exclude_tbl_list, ',',CONCAT_WS('',"','")),"')"); 35 PREPARE stmt FROM @exec_sql; 36 EXECUTE stmt; 37 DEALLOCATE PREPARE stmt; 38 END if; 39 ELSE 40 -- 插入静态字段 41 SET @exec_sql = CONCAT_WS('',"INSERT INTO temp_var_query_table SELECT t.table_name FROM information_schema.tables t WHERE table_schema=DATABASE() AND t.TABLE_NAME in ('", REPLACE(var_include_tbl_list, ',',CONCAT_WS('',"','")),"')"); 42 PREPARE stmt FROM @exec_sql; 43 EXECUTE stmt; 44 DEALLOCATE PREPARE stmt; 45 END if; 46 47 -- select @exec_sql; 48 49 -- 自定义查找，如果自定义查找，请注释掉上面默认的全库查找 50 51 -- INSERT INTO temp_query_table VALUES ('tbl_act_class'), ('tbl_act_info'); 52 53 54 /*-----------------------------------------------------------------------------*/ 55 /*---------------------配置项结束--------------------------------------*/ 56 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 57 /*-----------------------------------------------------------------------------*/ 58 59 60 SET group_concat_max_len = 4294967295; 61 62 -- select count(*) as "条数","tbl_cbm_app_entrance" as "表名","select count(*) from tbl_cbm_app_entrance where LEFT(CREATE_time,19)>'2021-08-04'" as "执行的脚本" from tbl_cbm_app_entrance where LEFT(CREATE_time,19)>'2021-08-04' 63 SET @query_code=' 64 select (@row_id:=@row_id+1) as "序号", count(*) as "num","@tbl_name" as "tbl_name","select count(*) from @tbl_name @var_where ;" as "执行的脚本" from @tbl_name, (select @row_id:=0 ) t @var_where 65 '; 66 SELECT REPLACE(@query_code,'@var_where',@var_where) INTO @query_code; 67 68 SELECT GROUP_CONCAT( 69 t.temp SEPARATOR '\r\n union all \r\n') INTO @var_query_sql 70 FROM 71 ( 72 SELECT 73 REPLACE(@query_code,'@tbl_name',t.TABLE_NAME) as temp 74 FROM information_schema.tables t 75 WHERE table_schema=DATABASE() AND t.table_name IN( SELECT * FROM temp_var_query_table WHERE tbl_name NOT IN('temp_var_query_table')) 76 ) t; 77 78 -- select @var_query_sql; 79 80 SET @exe_sql = @var_query_sql; 81 PREPARE stmt FROM @exe_sql; 82 EXECUTE stmt; 83 DEALLOCATE PREPARE stmt; 84 85 86 -- 组成建表语句 87 /*create table temp_var_tbl_name as 88 select t.tbl_name from 89 (select count(*) as num, 'tbl_act_black_white_list' as tbl_name from tbl_act_black_white_list where LEFT(CREATE_time,19)>'2021-08-04' 90 union all 91 select count(*) as num, 'tbl_act_card_group' as tbl_name from tbl_act_card_group where LEFT(CREATE_time,19)>'2021-08-04' 92 ) t where t.num>=1; 93 */ 94 95 DROP TABLE if exists temp_filter_table; 96 SET @exe_sql = CONCAT_WS('','create table temp_filter_table as select t.tbl_name from (',@var_query_sql,') t where t.num>=',var_greater_than_value); 97 98 PREPARE stmt FROM @exe_sql; 99 EXECUTE stmt; 100 DEALLOCATE PREPARE stmt; 101 102 -- select @exe_sql; 103 104 105 106 SELECT CONCAT( 107 'SET FOREIGN_KEY_CHECKS = 0;', 108 '\r\n', 109 GROUP_CONCAT( 110 CONCAT('drop table ',' ',tbl_name,'; ') 111 SEPARATOR '\r\n' 112 ), 113 '\r\n', 114 'SET FOREIGN_KEY_CHECKS = 1;' 115 ) INTO @drop_sql_code 116 FROM temp_filter_table; 117 118 SELECT CONCAT( 119 'SET FOREIGN_KEY_CHECKS = 0;', 120 '\r\n', 121 GROUP_CONCAT( 122 CONCAT_WS('','delete from ',tbl_name,' ',@var_where,';') 123 SEPARATOR '\r\n' 124 ), 125 '\r\n', 126 'SET FOREIGN_KEY_CHECKS = 1;' 127 ) INTO @delete_sql_code 128 FROM temp_filter_table; 129 130 SELECT CONCAT( 131 'SET FOREIGN_KEY_CHECKS = 0;', 132 '\r\n', 133 GROUP_CONCAT( 134 CONCAT_WS('','select * from ',tbl_name,' ',@var_where,';') 135 SEPARATOR '\r\n' 136 ), 137 '\r\n', 138 'SET FOREIGN_KEY_CHECKS = 1;' 139 ) INTO @select_sql_code 140 FROM temp_filter_table; 141 142 SELECT '代码','作用' LIMIT 0 143 UNION ALL 144 SELECT @select_sql_code ,'查询语句' 145 UNION ALL 146 SELECT @delete_sql_code,'删除语句' 147 UNION ALL 148 SELECT @drop_sql_code ,'drop表语句'; 149 150 DROP TABLE if exists temp_filter_table; 151 DROP TABLE if exists temp_var_query_table; 152 END %% 153 DELIMITER ; 154 155 -- SELECT * from temp_var_query_table; 156 157 -- CALL delete_drop_sql_generator(" where LEFT(CREATE_time,19)>'2021-08-04'",'','','0');
打印删除所有存储过程和数据库函数语句的存储过程
print_drop_all_proc_sql(
in_dbname [数据库名字 ]
）
1 -- 生成删除所有存储过程和函数的语句 2 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 3 DELIMITER $$ 4 DROP PROCEDURE IF EXISTS `print_drop_all_proc_sql`$$ 5 CREATE PROCEDURE `print_drop_all_proc_sql`(in_dbname VARCHAR(200)) 6 BEGIN 7 DECLARE var_count INT; 8 DECLARE var_name VARCHAR(200); 9 DECLARE var_type VARCHAR(200); 10 11 -- 获取所有的存储过程和函数 12 DECLARE pro_funcs CURSOR FOR SELECT routine_name,routine_type FROM information_schema.routines WHERE routine_schema = in_dbname; 13 SELECT COUNT(*) INTO var_count FROM information_schema.Routines WHERE routine_schema = in_dbname; 14 OPEN pro_funcs; 15 SET FOREIGN_KEY_CHECKS = 0; 16 SET @pro_func=NULL; 17 loop_i:LOOP 18 IF var_count = 0 THEN 19 LEAVE loop_i; 20 END IF; 21 FETCH pro_funcs INTO var_name,var_type; 22 -- 必须用concat_ws,不然有null,拼出来全部是空。char(10)或者“\r\n都可以” 23 SET @pro_func=CONCAT_WS('',@pro_func,' drop ',var_type, ' `',in_dbname,'`.`',var_name,'`;',"\r\n"); 24 25 SET var_count = var_count - 1; 26 END LOOP; 27 CLOSE pro_funcs; 28 SET FOREIGN_KEY_CHECKS = 1; 29 SELECT @pro_func; 30 END$$ 31 DELIMITER ;
本文来自博客园，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/15838755.html
