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');

delete_drop_sql陳述句生成器

本文來自博客園，作者：wanglifeng，轉載請注明原文鏈接：https://www.cnblogs.com/wanglifeng717/p/15838755.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/420437.html

標籤：MySQL

上一篇：insert陳述句生成的存盤程序

下一篇：基于mybatis的java代碼生成存盤程序