· 8 years ago · Jan 30, 2018, 10:42 AM
1DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o
2FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as c, jos_bet_1x2_prev as d,
3 jos_bet_1x3 as e, jos_bet_1x3_best as f, jos_bet_1x3_prev g, jos_bet_hcp as h,
4 jos_bet_hcp_best i, jos_bet_hcp_prev j, jos_bet_ou k,jos_bet_ou_best l,
5 jos_bet_ou_prev m, jos_bet_debug n, jos_bet_deleted o
6
7SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
8INTO @mass_delete_table_command
9FROM
10(
11 SELECT GROUP_CONCAT(table_name,'.*') delete_list,
12 GROUP_CONCAT(table_schema,'.',table_name) table_list
13 FROM
14 (
15 SELECT table_schema,table_name
16 FROM information_schema.tables
17 WHERE table_schema=database() AND
18 table_name IN
19 ('jos_bet_details','jos_bet_1x2','jos_bet_1x2_best','jos_bet_1x2_prev',
20 'jos_bet_1x3','jos_bet_1x3_best','jos_bet_1x3_prev','jos_bet_hcp',
21 'jos_bet_hcp_best','jos_bet_hcp_prev','jos_bet_ou','jos_bet_ou_best',
22 'jos_bet_ou_prev','jos_bet_debug','jos_bet_deleted')
23 ) ListOfTables
24) DeleteParameters;
25PREPARE s1 FROM @mass_delete_table_command;
26EXECUTE s1;
27DEALLOCATE PREPARE s1;
28
29use test
30DROP TABLE IF EXISTS t1;
31DROP TABLE IF EXISTS t2;
32DROP TABLE IF EXISTS t3;
33DROP TABLE IF EXISTS t4;
34CREATE TABLE t1 (n int NOT NULL);
35CREATE TABLE t2 like t1;
36CREATE TABLE t2 like t1;
37CREATE TABLE t3 like t1;
38CREATE TABLE t4 like t1;
39INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
40INSERT INTO t2 SELECT * FROM t1;
41INSERT INTO t3 SELECT * FROM t1;
42INSERT INTO t4 SELECT * FROM t1;
43SELECT A.*,B.*,C.*,D.* FROM
44(SELECT COUNT(1) t1count FROM t1) A,
45(SELECT COUNT(1) t2count FROM t2) B,
46(SELECT COUNT(1) t3count FROM t3) C,
47(SELECT COUNT(1) t4count FROM t4) D;
48SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
49INTO @mass_delete_table_command
50FROM
51(
52 SELECT GROUP_CONCAT(table_name,'.*') delete_list,
53 GROUP_CONCAT(table_schema,'.',table_name) table_list
54 FROM
55 (
56 SELECT table_schema,table_name
57 FROM information_schema.tables
58 WHERE table_schema=database() AND
59 table_name IN ('t1','t2','t3','t4')
60 ) ListOfTables
61) DeleteParameters;
62SELECT @mass_delete_table_command;
63PREPARE s1 FROM @mass_delete_table_command;
64EXECUTE s1;
65DEALLOCATE PREPARE s1;
66SELECT A.*,B.*,C.*,D.* FROM
67(SELECT COUNT(1) t1count FROM t1) A,
68(SELECT COUNT(1) t2count FROM t2) B,
69(SELECT COUNT(1) t3count FROM t3) C,
70(SELECT COUNT(1) t4count FROM t4) D;
71
72mysql> use test
73DROP TABLE IF EXISTS t1;
74DROP TABLE IF EXISTS t2;
75DROP TABLE IF EXISTS t3;
76Database changed
77mysql> DROP TABLE IF EXISTS t1;
78CREATE TABLE t1 (n int NOT NULL);
79CREATE TABLE t2 like t1;
80CREATE TABLE t2 like t1;
81CREATE TABLE t3 like t1;
82CREATE TABLE t4 like t1;
83INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
84INSERT INTO t2 SELECT * FROM t1;
85INSERT INTO t3 SELECT * FROM t1;
86INSERT INTO t4 SELECT * FROM t1;
87SELECT A.*,B.*,C.*,D.* FROM
88(SELECT COUNT(1) t1count FROM t1) A,
89(SELECT COUNT(1) t2count FROM t2) B,
90(SELECT COUNT(1) t3count FROM t3) C,
91(SELECT COUNT(1) t4count FROM t4) D;
92SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
93INTO @mass_delete_table_command
94FROM
95(
96 SELECT GROUP_CONCAT(table_name,'.*') delete_list,
97 GROUP_CONCAT(table_schema,'.',table_name) table_list
98 FROM
99 (
100 SELECT table_schema,table_name
101 FROM information_schema.tables
102 WHERE table_schema=database() AND
103 table_name IN ('t1','t2','t3','t4')
104 ) ListOfTables
105) DeleteParameters;
106SELECT @mass_delete_table_command;
107PREPARE s1 FROM @mass_delete_table_command;
108EXECUTE s1;
109DEALLOCATE PREPARE s1;
110SELECT A.*,B.*,C.*,D.* FROM
111(SELECT COUNT(1) t1count FROM t1) A,
112(SELECT COUNT(1) t2count FROM t2) B,
113(SELECT COUNT(1) t3count FROM t3) C,
114(SELECT COUNT(1) t4count FROM t4) D;
115Query OK, 0 rows affected (0.04 sec)
116
117mysql> DROP TABLE IF EXISTS t2;
118Query OK, 0 rows affected (0.03 sec)
119
120mysql> DROP TABLE IF EXISTS t3;
121Query OK, 0 rows affected (0.03 sec)
122
123mysql> DROP TABLE IF EXISTS t4;
124Query OK, 0 rows affected (0.03 sec)
125
126mysql> CREATE TABLE t1 (n int NOT NULL);
127Query OK, 0 rows affected (0.07 sec)
128
129mysql> CREATE TABLE t2 like t1;
130Query OK, 0 rows affected (0.10 sec)
131
132mysql> CREATE TABLE t2 like t1;
133ERROR 1050 (42S01): Table 't2' already exists
134mysql> CREATE TABLE t3 like t1;
135Query OK, 0 rows affected (0.07 sec)
136
137mysql> CREATE TABLE t4 like t1;
138Query OK, 0 rows affected (0.25 sec)
139
140mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
141Query OK, 7 rows affected (0.08 sec)
142Records: 7 Duplicates: 0 Warnings: 0
143
144mysql> INSERT INTO t2 SELECT * FROM t1;
145Query OK, 7 rows affected (0.05 sec)
146Records: 7 Duplicates: 0 Warnings: 0
147
148mysql> INSERT INTO t3 SELECT * FROM t1;
149Query OK, 7 rows affected (0.07 sec)
150Records: 7 Duplicates: 0 Warnings: 0
151
152mysql> INSERT INTO t4 SELECT * FROM t1;
153Query OK, 7 rows affected (0.06 sec)
154Records: 7 Duplicates: 0 Warnings: 0
155
156mysql> SELECT A.*,B.*,C.*,D.* FROM
157 -> (SELECT COUNT(1) t1count FROM t1) A,
158 -> (SELECT COUNT(1) t2count FROM t2) B,
159 -> (SELECT COUNT(1) t3count FROM t3) C,
160 -> (SELECT COUNT(1) t4count FROM t4) D;
161+---------+---------+---------+---------+
162| t1count | t2count | t3count | t4count |
163+---------+---------+---------+---------+
164| 7 | 7 | 7 | 7 |
165+---------+---------+---------+---------+
1661 row in set (0.00 sec)
167
168mysql> SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
169 -> INTO @mass_delete_table_command
170 -> FROM
171 -> (
172 -> SELECT GROUP_CONCAT(table_name,'.*') delete_list,
173 -> GROUP_CONCAT(table_schema,'.',table_name) table_list
174 -> FROM
175 -> (
176 -> SELECT table_schema,table_name
177 -> FROM information_schema.tables
178 -> WHERE table_schema=database() AND
179 -> table_name IN ('t1','t2','t3','t4')
180 -> ) ListOfTables
181 -> ) DeleteParameters;
182Query OK, 1 row affected (0.00 sec)
183
184mysql> SELECT @mass_delete_table_command;
185+-----------------------------------------------------------------+
186| @mass_delete_table_command |
187+-----------------------------------------------------------------+
188| DELETE t1.*,t2.*,t3.*,t4.* FROM test.t1,test.t2,test.t3,test.t4 |
189+-----------------------------------------------------------------+
1901 row in set (0.00 sec)
191
192mysql> PREPARE s1 FROM @mass_delete_table_command;
193Query OK, 0 rows affected (0.00 sec)
194Statement prepared
195
196mysql> EXECUTE s1;
197Query OK, 28 rows affected (0.08 sec)
198
199mysql> DEALLOCATE PREPARE s1;
200Query OK, 0 rows affected (0.00 sec)
201
202mysql> SELECT A.*,B.*,C.*,D.* FROM
203 -> (SELECT COUNT(1) t1count FROM t1) A,
204 -> (SELECT COUNT(1) t2count FROM t2) B,
205 -> (SELECT COUNT(1) t3count FROM t3) C,
206 -> (SELECT COUNT(1) t4count FROM t4) D;
207+---------+---------+---------+---------+
208| t1count | t2count | t3count | t4count |
209+---------+---------+---------+---------+
210| 0 | 0 | 0 | 0 |
211+---------+---------+---------+---------+
2121 row in set (0.00 sec)
213
214mysql>
215
216declare @sql nvarchar(max), @enter char(2);
217set @enter = char(13)+char(10);
218set @sql = '';
219
220select @sql = @sql + 'delete from ' + table_schema + '.' + table_name + ';' + @enter
221from information_schema.tables
222
223print @sql;
224--exec @sql; --only after review!!!
225
226create table t1(v1 serial);
227create table t2(v2 serial);
228insert into t1 values(default);
229insert into t2 values(default);
230
231select count(*) from (select * from t1 union all select * from t2) z;
232+----------+
233| count(*) |
234+----------+
235| 2 |
236+----------+
237
238delete a, b from t1 as a, t2 as b;
239
240select count(*) from (select * from t1 union all select * from t2) z;
241+----------+
242| count(*) |
243+----------+
244| 0 |
245+----------+
246
247SELECT delete_list /*CONCAT('DELETE ',delete_list,' FROM ',table_list)*/
248INTO @mass_delete_table_command
249FROM
250(
251 SELECT GROUP_CONCAT('Drop table ',table_name,'; ') delete_list,
252 GROUP_CONCAT(table_schema,'.',table_name) table_list
253 FROM
254 (
255 SELECT table_schema,table_name
256 FROM information_schema.tables
257 WHERE table_name LIKE '%$%'
258 AND table_schema = 'you schema name'
259 ) ListOfTables
260) DeleteParameters;
261SELECT @mass_delete_table_command;