deletedrop语句生成的存储过程
问题：
开发时有时候需要对很多表进行操作。
例如：清空(删除)所有(某些)表，删除所有表某时间点之后导入的数据(类似于回滚)
解决方式：
对选定的表集合执行相同的查询条件(可为空)，如果这个执行结果大于阈值，则使用相同的条件组建delete语句" title="delete语句">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/wanglifeng7172DROPPROCEDUREIFEXISTS delete_drop_sql_generator;
3 DELIMITER %%
4CREATEPROCEDURE 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适用场景：
111.将所有表2021-08-01日插入的记录全部删除。例如刚配置业务数据全部删除，只要确定某个时间段，只有你的数据在里面
12
132.将某些表相同字段的记录删除，自定义哪些表，必须同时含有where条件中的字段。
14*/
15DROPTABLEifexists temp_filter_table;
16DROPTABLEifexists temp_var_query_table;
17CREATEtable temp_var_query_table(tbl_name VARCHAR(512));
18
19/*-----------------------------------------------------------------------------*/
20-- ----------配置项目-----------
21-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
22/*-----------------------------------------------------------------------------*/
23SET@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为准。
28if(var_include_tbl_list ISNULLOR var_include_tbl_list="include_tbl_list"OR var_include_tbl_list=""OR var_include_tbl_list=""OR var_include_tbl_list="") then
29if(var_exclude_tbl_list ISNULLOR var_exclude_tbl_list="exclude_tbl_list"OR var_exclude_tbl_list=""OR var_exclude_tbl_list=""OR var_exclude_tbl_list="") then
30-- 如果包含和不含字段都是为空，将库中所有的表加入进去。
31INSERTINTO 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";
32else
33-- 如果include为空，但是exclude不为空
34SET@exec_sql= CONCAT_WS("","INSERTINTO temp_var_query_table SELECT t.table_name FROM information_schema.tables t WHERE table_schema=DATABASE() AND t.TABLE_NAME notin ("", REPLACE(var_exclude_tbl_list, ",",CONCAT_WS("","","")),"")");
35PREPARE stmt FROM@exec_sql;
36EXECUTE stmt;
37DEALLOCATEPREPARE stmt;
38ENDif;
39ELSE
40-- 插入静态字段
41SET@exec_sql= CONCAT_WS("","INSERTINTO 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("","","")),"")");
42PREPARE stmt FROM@exec_sql;
43EXECUTE stmt;
44DEALLOCATEPREPARE stmt;
45ENDif;
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
60SET 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"
63SET@query_code="
64select (@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";
66SELECTREPLACE(@query_code,"@var_where",@var_where) INTO@query_code;
67
68SELECT GROUP_CONCAT(
69 t.temp SEPARATOR "
union all
") INTO@var_query_sql
70FROM
71(
72SELECT
73REPLACE(@query_code,"@tbl_name",t.TABLE_NAME) astemp
74FROM information_schema.tables t
75WHERE table_schema=DATABASE() AND t.table_name IN( SELECT*FROM temp_var_query_table WHERE tbl_name NOTIN("temp_var_query_table"))
76 ) t;
77
78-- select @var_query_sql;
79
80SET@exe_sql=@var_query_sql;
81PREPARE stmt FROM@exe_sql;
82EXECUTE stmt;
83DEALLOCATEPREPARE stmt;
84
85
86-- 组成建表语句
87/*create table temp_var_tbl_name as
88select 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"
90union all
91select 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
95DROPTABLEifexists temp_filter_table;
96SET@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
98PREPARE stmt FROM@exe_sql;
99EXECUTE stmt;
100DEALLOCATEPREPARE stmt;
101
102-- select @exe_sql;
103
104
105
106SELECT CONCAT(
107"SET FOREIGN_KEY_CHECKS = 0;",
108"
",
109 GROUP_CONCAT(
110 CONCAT("drop table ","",tbl_name,"; ")
111 SEPARATOR "
"
112 ),
113"
",
114"SET FOREIGN_KEY_CHECKS = 1;"
115 ) INTO@drop_sql_code
116FROM temp_filter_table;
117
118SELECT CONCAT(
119"SET FOREIGN_KEY_CHECKS = 0;",
120"
",
121 GROUP_CONCAT(
122 CONCAT_WS("","delete from ",tbl_name,"",@var_where,";")
123 SEPARATOR "
"
124 ),
125"
",
126"SET FOREIGN_KEY_CHECKS = 1;"
127 ) INTO@delete_sql_code
128FROM temp_filter_table;
129
130SELECT CONCAT(
131"SET FOREIGN_KEY_CHECKS = 0;",
132"
",
133 GROUP_CONCAT(
134 CONCAT_WS("","select * from ",tbl_name,"",@var_where,";")
135 SEPARATOR "
"
136 ),
137"
",
138"SET FOREIGN_KEY_CHECKS = 1;"
139 ) INTO@select_sql_code
140FROM temp_filter_table;
141
142SELECT"代码","作用" LIMIT 0
143UNIONALL
144SELECT@select_sql_code ,"查询语句"
145UNIONALL
146SELECT@delete_sql_code,"删除语句"
147UNIONALL
148SELECT@drop_sql_code ,"drop表语句";
149
150DROPTABLEifexists temp_filter_table;
151DROPTABLEifexists temp_var_query_table;
152END%%
153DELIMITER ;
154
155-- SELECT * from temp_var_query_table;
156
157-- CALL delete_drop_sql_generator(" where LEFT(CREATE_time,19)>"2021-08-04"","","","0");
delete_drop_sql语句生成器
本文来自云海天，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/15838755.html
以上是 deletedrop语句生成的存储过程 的全部内容， 来源链接： utcz.com/z/536254.html
