· 4 years ago · Mar 09, 2021, 06:36 PM
1-- ------------------------------------------------------------------------------------
2-- USAGE
3-- ------------------------------------------------------------------------------------
4-- CALL delete_recursive(<schema name>, <table name>, <WHERE clause>, <delete flag>);
5-- where:
6-- <schema name> is the name of the MySQL schema
7-- <table name> is the name of the base table to delete records from
8-- <WHERE clase> is a SQL WHERE clause to filter which records that are to be deleted
9-- <delete flag> is either TRUE or FALSE: If TRUE, the records *will* be deleted.
10-- If FALSE, the SQL will be output without actually deleting anything.
11-- Example:
12-- CALL delete_recursive('mydb', 'mytable', 'WHERE mypk IN (1, 2, 3)', TRUE);
13DROP PROCEDURE IF EXISTS delete_recursive;
14DELIMITER //
15CREATE PROCEDURE delete_recursive(schema_name VARCHAR(64),
16 tbl_name VARCHAR(64),
17 where_clause TEXT,
18 do_delete BIT)
19BEGIN
20 DECLARE next_schema_name, next_tbl_name VARCHAR(64);
21 DECLARE from_clause, next_where_clause, next_col_names, ref_col_names TEXT;
22 DECLARE done INT DEFAULT FALSE;
23 DECLARE cursor1 CURSOR FOR
24 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAMES, REF_COLUMN_NAMES FROM temp_kcu;
25 DECLARE cursor2 CURSOR FOR
26 SELECT table_schema, table_name, where_sql FROM temp_deletes ORDER BY id;
27 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
28
29 -- Set maximum recursion depth
30 SET @@SESSION.max_sp_recursion_depth = 255;
31
32 -- Increment current recursion depth since the stored procedure has been entered.
33 SET @recursion_depth = IFNULL(@recursion_depth + 1, 0);
34
35 -- Create temporary table for storing the deletes if it doesn't already exist
36 IF @recursion_depth = 0 THEN
37 DROP TEMPORARY TABLE IF EXISTS temp_deletes;
38 CREATE TEMPORARY TABLE temp_deletes (
39 id INT NOT NULL AUTO_INCREMENT,
40 table_schema VARCHAR(64),
41 table_name VARCHAR(64),
42 where_sql TEXT,
43 Notes TEXT,
44 PRIMARY KEY(id)
45 );
46 END IF;
47
48 -- Construct FROM clause (including the WHERE clause) for this table.
49 SET from_clause =
50 CONCAT(' FROM ', schema_name, '.', tbl_name, ' WHERE ', where_clause);
51
52 -- Find out whether there are any foreign keys to this table
53 SET @query = CONCAT('SELECT COUNT(*) INTO @count', from_clause);
54 PREPARE stmt FROM @query;
55 EXECUTE stmt;
56 DEALLOCATE PREPARE stmt;
57
58 IF @count > 0 THEN
59 -- There are foriegn keys to this table so all linked rows must be deleted first:
60 -- Firstly, fill a temporary table with the foreign key metadata.
61 DROP TEMPORARY TABLE IF EXISTS temp_kcu;
62 SET @query = CONCAT(
63 'CREATE TEMPORARY TABLE temp_kcu AS ',
64 'SELECT TABLE_SCHEMA, TABLE_NAME, ',
65 'GROUP_CONCAT(CONCAT(COLUMN_NAME) SEPARATOR '', '') AS COLUMN_NAMES, ',
66 'GROUP_CONCAT(CONCAT(REFERENCED_COLUMN_NAME) SEPARATOR '', '')
67 AS REF_COLUMN_NAMES ',
68 'FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ',
69 'WHERE REFERENCED_TABLE_SCHEMA = ''', schema_name,
70 ''' AND REFERENCED_TABLE_NAME = ''', tbl_name, ''' ',
71 'GROUP BY CONSTRAINT_NAME');
72 PREPARE stmt FROM @query;
73 EXECUTE stmt;
74 DEALLOCATE PREPARE stmt;
75
76 -- Loop through all foreign keys to this table using a cursor.
77 OPEN cursor1;
78 read_loop: LOOP
79 FETCH cursor1 INTO next_schema_name, next_tbl_name, next_col_names,
80 ref_col_names;
81 IF done THEN
82 -- No more rows so exit the loop.
83 LEAVE read_loop;
84 END IF;
85
86 -- Recursively call the stored procedure to delete linked rows
87 -- for this foreign key.
88 IF INSTR(next_col_names, ',') = 0 THEN
89 SET next_where_clause = CONCAT(
90 next_col_names, ' IN (SELECT ', ref_col_names, from_clause, ')');
91 ELSE
92 SET next_where_clause = CONCAT(
93 '(', next_col_names, ') IN (SELECT ', ref_col_names, from_clause, ')');
94 END IF;
95 CALL delete_recursive(
96 next_schema_name, next_tbl_name, next_where_clause, do_delete);
97 END LOOP;
98 CLOSE cursor1;
99 END IF;
100
101 -- Build the DELETE statement
102 SET @query = CONCAT(
103 'DELETE FROM ', schema_name, '.', tbl_name, ' WHERE ', where_clause);
104
105 -- Get the number of primary key columns
106 SET @pk_column_count = (SELECT COUNT(*)
107 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
108 WHERE TABLE_SCHEMA = schema_name
109 AND TABLE_NAME = tbl_name
110 AND CONSTRAINT_NAME = 'PRIMARY');
111 IF @pk_column_count = 0 THEN
112 -- No primary key so just output the number of rows to be deleted
113 SET @query = CONCAT(
114 'SET @notes = CONCAT(''No primary key; number of rows to delete = '',
115 (SELECT COUNT(*) FROM ', schema_name, '.', tbl_name, ' WHERE ',
116 where_clause, '))');
117 PREPARE stmt FROM @query;
118 EXECUTE stmt;
119 DEALLOCATE PREPARE stmt;
120 ELSEIF @pk_column_count = 1 THEN
121 -- 1 primary key column.
122 -- Output the primary keys of the records to be deleted
123 SET @pk_column = (SELECT COLUMN_NAME
124 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
125 WHERE TABLE_SCHEMA = schema_name
126 AND TABLE_NAME = tbl_name
127 AND CONSTRAINT_NAME = 'PRIMARY');
128 SET @pk_column_csv = CONCAT('CONCAT('''''''', ', @pk_column, ', '''''''')');
129 SET @query = CONCAT(
130 'SET @notes = (SELECT CONCAT(''', @pk_column, ' IN ('', GROUP_CONCAT(',
131 @pk_column_csv, ' SEPARATOR '', ''), '')'') FROM ',
132 schema_name, '.', tbl_name, ' WHERE ', where_clause, ')');
133 PREPARE stmt FROM @query;
134 EXECUTE stmt;
135 DEALLOCATE PREPARE stmt;
136 ELSE
137 -- Multiple primary key columns.
138 -- Output the primary keys of the records to be deleted.
139 SET @pk_columns = (SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ')
140 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
141 WHERE TABLE_SCHEMA = schema_name
142 AND TABLE_NAME = tbl_name
143 AND CONSTRAINT_NAME = 'PRIMARY');
144 SET @pk_columns_csv = (SELECT CONCAT('CONCAT(''('''''', ', GROUP_CONCAT(COLUMN_NAME
145 SEPARATOR ', '''''', '''''', '), ', '''''')'')')
146 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
147 WHERE TABLE_SCHEMA = schema_name
148 AND TABLE_NAME = tbl_name
149 AND CONSTRAINT_NAME = 'PRIMARY');
150 SET @query = CONCAT(
151 'SET @notes = (SELECT CONCAT(''(', @pk_columns,
152 ') IN ('', GROUP_CONCAT(', @pk_columns_csv, ' SEPARATOR '', ''), '')'') FROM ',
153 schema_name, '.', tbl_name, ' WHERE ', where_clause, ')');
154 PREPARE stmt FROM @query;
155 EXECUTE stmt;
156 DEALLOCATE PREPARE stmt;
157 END IF;
158
159 IF @notes IS NULL THEN
160 SET @notes = 'No affected rows.';
161 END IF;
162
163 -- Save details of the DELETE statement to be executed
164 INSERT INTO temp_deletes (table_schema, table_name, where_sql, Notes)
165 VALUES (schema_name, tbl_name, where_clause, @notes);
166
167 IF @recursion_depth = 0 THEN
168 -- Output the deletes.
169 SELECT CONCAT('DELETE FROM ', schema_name, '.', table_name,
170 ' WHERE ', where_sql) `SQL`,
171 Notes
172 FROM temp_deletes ORDER BY id;
173
174 IF do_delete THEN
175 -- Perform the deletes: Loop through all delete queries using a cursor.
176 SET done = FALSE;
177 OPEN cursor2;
178 read_loop: LOOP
179 FETCH cursor2 INTO schema_name, tbl_name, where_clause;
180 IF done THEN
181 -- No more rows so exit the loop.
182 LEAVE read_loop;
183 END IF;
184
185 SET @query = CONCAT(
186 'DELETE FROM ', schema_name, '.', tbl_name, ' WHERE ', where_clause);
187
188 PREPARE stmt FROM @query;
189 EXECUTE stmt;
190 DEALLOCATE PREPARE stmt;
191 END LOOP;
192 CLOSE cursor2;
193 END IF;
194
195 -- Tidy up
196 DROP TEMPORARY TABLE IF EXISTS temp_deletes;
197 END IF;
198
199 -- Decrement current recursion depth since the stored procedure is being exited.
200 SET @recursion_depth = @recursion_depth - 1;
201END;//
202DELIMITER ;