Início / dba / Perguntas / 4984
Accepted
Mokus
Asked: 2011-08-24 14:02:45 +0800 CST

Limpe todas as tabelas com um DELETE

  • 772

É possível limpar todas as tabelas com uma solicitação? Eu tentei o seguinte sem resultado.

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 respostas

  1. Best Answer

    Aqui está sua solução

    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;

    Aqui está o princípio por trás disso: você deseja usar information_schema.tables para criar a consulta DELETE dinamicamente para você.

    Primeiro, colete os nomes das tabelas em uma variável chamada 'mass_delete_table_command' como uma lista de exclusão e uma lista de tabelas. Em seguida, execute-o como uma instrução preparada.

    Aqui está o código de amostra da criação de 4 tabelas, adicionando registros e zapeando os registros:

    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;

    Eu corri isso no MySQL 5.5.12 no meu PC. Aqui está a saída:

    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>

    Isso definitivamente funcionará para você, desde que

    • você está no banco de dados correto no momento
    • você fornece a lista de tabelas que deseja descartar nas subconsultas

    De uma chance !!!

    • 5

  2. Sua deletesintaxe parece boa para mim:

    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 |
+----------+

    Observe que as restrições de chave estrangeira podem fazer com que isso falhe . Se você tiver chaves estrangeiras circulares (ou seja, da tabela A para a tabela B e da tabela B para a tabela A), talvez seja necessário fazer exclusões em cascata ou descartar (ou desabilitar ) as restrições antes de excluir.

    • 4

  3. Você teria que construir uma instrução sql dinâmica que excluiria dados de todas as tabelas, uma por uma. Você encontrará um exemplo adequado de truncamento de dados em todas as tabelas em um banco de dados nesta questão . O problema é truncar todas as tabelas em um banco de dados MS SQL 2000 usando Transact-Sql. Também falamos sobre truncar tabelas (operação não logada, tem algumas limitações), não apenas deletar, então se você precisar deletar, terá que mudar a operação de truncar para deletar.

    O problema aqui está relacionado com a ordem de precedência das declarações ao olhar para o relacionamento pai/filho entre as tabelas, pois você não pode esvaziar uma tabela pai antes de seus filhos, então você terá que verificar a ordem da exclusão declarações.

    eu faria algo como:

    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!!!

    Este é um exemplo T-SQL de sql dinâmico usando system view information_schema.tables que também existe em My-SQL. Você terá que traduzir a declaração da variável e outros pequenos problemas de linguagem.

    EDIT 1 - razão para exclusão: eu não estava ciente da instrução DELETE de várias tabelas do MySQL . Foi mal!

    Editar 2 - motivo da exclusão: vi que a resposta aceita estava usando essa técnica, então parece que essa resposta é válida. O que aconteceu com a instrução de exclusão de várias tabelas?

    • 2

  4. Eu reutilizei a resposta RonaldoMYSQL db para descartar uma tabela completamente, postando-a aqui para que qualquer pessoa possa usá-la para sua conveniência,

    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;

    Uma vírgula é buscada em minha consulta, então modifiquei essa instrução com uma substituição total e a executei manualmente.

    • 0

relate perguntas