主页 / dba / 问题 / 4984
Accepted
Mokus
Asked: 2011-08-24 14:02:45 +0800 CST

用一个 DELETE 清除所有表

  • 772

是否可以一次请求清除所有表？我试过以下没有结果。

DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o
FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as c, jos_bet_1x2_prev as d,
     jos_bet_1x3 as e, jos_bet_1x3_best as f, jos_bet_1x3_prev g, jos_bet_hcp as h,
     jos_bet_hcp_best i, jos_bet_hcp_prev j, jos_bet_ou k,jos_bet_ou_best l,
     jos_bet_ou_prev m, jos_bet_debug n, jos_bet_deleted o
mysql delete

4 个回答

  1. Best Answer

    这是您的解决方案

    SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN
        ('jos_bet_details','jos_bet_1x2','jos_bet_1x2_best','jos_bet_1x2_prev',
         'jos_bet_1x3','jos_bet_1x3_best','jos_bet_1x3_prev','jos_bet_hcp',
         'jos_bet_hcp_best','jos_bet_hcp_prev','jos_bet_ou','jos_bet_ou_best',
         'jos_bet_ou_prev','jos_bet_debug','jos_bet_deleted')
    ) ListOfTables
) DeleteParameters;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;

    这背后的原理是：您想使用information_schema.tables为您动态构建 DELETE 查询。

    首先在名为“mass_delete_table_command”的变量中收集表的名称作为删除列表和表列表。然后，将其作为准备好的语句执行。

    以下是创建 4 个表、添加记录并切换记录的示例代码：

    use test
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
CREATE TABLE t1 (n int NOT NULL);
CREATE TABLE t2 like t1;
CREATE TABLE t2 like t1;
CREATE TABLE t3 like t1;
CREATE TABLE t4 like t1;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
INSERT INTO t4 SELECT * FROM t1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN ('t1','t2','t3','t4')
    ) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;

    我在我的 PC 上的 MySQL 5.5.12 中运行了它。这是输出：

    mysql> use test
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
Database changed
mysql> DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (n int NOT NULL);
CREATE TABLE t2 like t1;
CREATE TABLE t2 like t1;
CREATE TABLE t3 like t1;
CREATE TABLE t4 like t1;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
INSERT INTO t4 SELECT * FROM t1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN ('t1','t2','t3','t4')
    ) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
Query OK, 0 rows affected (0.04 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t3;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t4;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1 (n int NOT NULL);
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t2 like t1;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE t2 like t1;
ERROR 1050 (42S01): Table 't2' already exists
mysql> CREATE TABLE t3 like t1;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t4 like t1;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
Query OK, 7 rows affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t3 SELECT * FROM t1;
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t4 SELECT * FROM t1;
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT A.*,B.*,C.*,D.* FROM
    -> (SELECT COUNT(1) t1count FROM t1) A,
    -> (SELECT COUNT(1) t2count FROM t2) B,
    -> (SELECT COUNT(1) t3count FROM t3) C,
    -> (SELECT COUNT(1) t4count FROM t4) D;
+---------+---------+---------+---------+
| t1count | t2count | t3count | t4count |
+---------+---------+---------+---------+
|       7 |       7 |       7 |       7 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
    -> INTO @mass_delete_table_command
    -> FROM
    -> (
    ->     SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    ->     GROUP_CONCAT(table_schema,'.',table_name) table_list
    ->     FROM
    ->     (
    ->         SELECT table_schema,table_name
    ->         FROM information_schema.tables
    ->         WHERE table_schema=database() AND
    ->         table_name IN ('t1','t2','t3','t4')
    ->     ) ListOfTables
    -> ) DeleteParameters;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @mass_delete_table_command;
+-----------------------------------------------------------------+
| @mass_delete_table_command                                      |
+-----------------------------------------------------------------+
| DELETE t1.*,t2.*,t3.*,t4.* FROM test.t1,test.t2,test.t3,test.t4 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s1 FROM @mass_delete_table_command;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s1;
Query OK, 28 rows affected (0.08 sec)

mysql> DEALLOCATE PREPARE s1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.*,B.*,C.*,D.* FROM
    -> (SELECT COUNT(1) t1count FROM t1) A,
    -> (SELECT COUNT(1) t2count FROM t2) B,
    -> (SELECT COUNT(1) t3count FROM t3) C,
    -> (SELECT COUNT(1) t4count FROM t4) D;
+---------+---------+---------+---------+
| t1count | t2count | t3count | t4count |
+---------+---------+---------+---------+
|       0 |       0 |       0 |       0 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql>

    这肯定会为你工作，只要

    • 您当时站在正确的数据库中
    • 您提供要删除到子查询的表列表

    试试看 ！！！

    • 5

  2. 你的delete语法对我来说看起来不错：

    create table t1(v1 serial);
create table t2(v2 serial);
insert into t1 values(default);
insert into t2 values(default);

select count(*) from (select * from t1 union all select * from t2) z;
+----------+
| count(*) |
+----------+
|        2 |
+----------+

delete a, b from t1 as a, t2 as b;

select count(*) from (select * from t1 union all select * from t2) z;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

    请注意，外键约束可能会导致此操作失败。如果您有循环外键（即从表 A 到表 B 以及从表 B 到表 A），那么您可能需要在删除之前进行级联删除或删除（或禁用）约束。

    • 4

  3. 您必须构建一个动态 sql 语句，该语句将从所有表中逐一删除数据。您将在此问题中找到有关截断数据库中所有表中数据的适当示例。那里的问题是关于使用 Transact-Sql 截断 MS SQL 2000 数据库中的所有表。此外，我们还讨论了截断表（未记录的操作，有一些限制），而不仅仅是删除，所以如果您需要删除，您必须将操作从截断更改为删除。

    这里的问题与查看表之间的父/子关系时语句的优先顺序有关，因为您不能在其子表之前清空父表，因此您必须检查删除的顺序陈述。

    我会做类似的事情：

    declare @sql nvarchar(max), @enter char(2);
set @enter = char(13)+char(10);
set @sql = '';

select @sql = @sql + 'delete from ' + table_schema + '.' + table_name + ';' + @enter
from information_schema.tables

print @sql;
--exec @sql; --only after review!!!

    这是使用系统视图 information_schema.tables 的动态 sql 的 T-SQL 示例，它也存在于 My-SQL 中。您必须翻译变量声明和其他小语言问题。

    编辑 1 - 删除原因：我不知道 MySQL 的多表 DELETE 语句。我的错！

    编辑 2 - 取消删除的原因：我看到接受的答案正在使用这种技术，所以看起来这个答案是有效的。多表删除语句发生了什么？

    • 2

  4. 我已经重新使用了 RonaldoMYSQL 数据库答案来完全删除一个表，将其发布在这里，以便任何人都可以使用它来方便他/她，

    SELECT delete_list   /*CONCAT('DELETE ',delete_list,' FROM ',table_list)*/
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT('Drop table ',table_name,'; ') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_name LIKE '%$%' 
        AND table_schema = 'you schema name'       
    ) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;

    在我的查询中提取了一个逗号，所以我用全部替换修改了该语句，然后手动执行它。

    • 0

相关问题