问题: 开发时有时候需要对很多表进行操作。 例如:清空(删除)所有(某些)表,删除所有表某时间点之后导入的数据(类似于回滚) 解决方式: 对选定的表集合执行相同的查询条件(可为空),如果这个执行结果大于阈值
问题:
开发时有时候需要对很多表进行操作。
例如:清空(删除)所有(某些)表,删除所有表某时间点之后导入的数据(类似于回滚)
解决方式:
对选定的表集合执行相同的查询条件(可为空),如果这个执行结果大于阈值,则使用相同的条件组建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
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 "
uNIOn all
") 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
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 "
",
109 GROUP_CONCAT(
110 CONCAT("drop table "," ",tbl_name,"; ")
111 SEPARATOR "
"
112 ),
113 "
",
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 "
",
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
128 FROM temp_filter_table;
129
130 SELECT 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
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");
delete_drop_sql语句生成器
本文来自云海天,作者:wanglifeng,转载请注明原文链接:https://www.cnblogs.com/wanglifeng717/p/15838755.html
--结束END--
本文标题: delete-drop语句生成的存储过程
本文链接: https://www.lsjlt.com/news/9084.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0