vaciar - ¿Truncar todas las tablas en una base de datos MySQL en un comando?

truncate mysql

Question

¿Existe una consulta (comando) para truncar todas las tablas en una base de datos en una operación? Quiero saber si puedo hacer esto con una sola consulta.

Answer 1

Drop (es decir, eliminar tablas)

mysql -Nse ''show tables'' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done

Truncar (es decir, tablas vacías)

mysql -Nse ''show tables'' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done

Answer 2

1. Para truncar una tabla, se deben eliminar las restricciones de clave externa asignadas a las columnas en esta tabla desde otras tablas (de hecho, en todas las tablas en el DB / esquema específico).
2. Por lo tanto, todas las restricciones de clave externa se deben eliminar inicialmente, seguidas del truncamiento de la tabla.
3. Opcionalmente, use la tabla de optimización (en mysql, innodb engine esp) para reclamar el espacio / tamaño de datos utilizados al sistema operativo después del truncamiento de datos.

4. Una vez que se lleva a cabo el truncamiento de datos, vuelva a crear las mismas restricciones de clave externa en la misma tabla. Vea a continuación un script que generaría el script para llevar a cabo las operaciones anteriores.

   SELECT CONCAT(''ALTER TABLE '',TABLE_SCHEMA,''.'',TABLE_NAME,'' DROP FOREIGN KEY '',CONSTRAINT_NAME,'';'') FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_TYPE=''FOREIGN KEY'' AND TABLE_SCHEMA=''<TABLE SCHEMA>''
   UNION
   SELECT CONCAT(''TRUNCATE TABLE '',TABLE_SCHEMA,''.'',TABLE_NAME,'';'') FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA=''<TABLE SCHEMA>'' AND TABLE_TYPE=''BASE TABLE''
   UNION
   SELECT CONCAT(''OPTIMIZE TABLE '',TABLE_SCHEMA,''.'',TABLE_NAME,'';'') FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA=''<TABLE SCHEMA>'' AND TABLE_TYPE=''BASE TABLE''
   UNION
   SELECT CONCAT(''ALTER TABLE '',TABLE_SCHEMA,''.'',TABLE_NAME,'' ADD CONSTRAINT '',CONSTRAINT_NAME,'' FOREIGN KEY('',COLUMN_NAME,'')'','' REFERENCES '',REFERENCED_TABLE_NAME,''('',REFERENCED_COLUMN_NAME,'');'') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE CONSTRAINT_NAME LIKE ''FK%'' AND TABLE_SCHEMA=''<TABLE SCHEMA>''
   INTO OUTFILE "C:/DB Truncate.sql" LINES TERMINATED BY ''/n'';

Ahora, ejecute el script Db Truncate.sql generado

Beneficios 1) Recupere espacio en el disco 2) No es necesario descartar y volver a crear el DB / esquema con la misma estructura

Inconvenientes 1) Las restricciones FK deben ser nombres en la tabla con el nombre que contiene ''FK'' en el nombre de la restricción.

Answer 3

¿Una idea podría ser simplemente soltar y recrear las tablas?

EDITAR:

@Jonathan Leffler: Verdadero

Otra sugerencia (o caso no necesita truncar TODAS las tablas):

¿Por qué no crear un procedimiento almacenado básico para truncar tablas específicas?

CREATE PROCEDURE [dbo].[proc_TruncateTables]
AS
TRUNCATE TABLE Table1
TRUNCATE TABLE Table2
TRUNCATE TABLE Table3
GO

Answer 4

Aquí está mi variante para tener ''una declaración para truncarlos'' a todos ''.

Primero, estoy usando una base de datos separada llamada ''util'' para los procedimientos almacenados de mi ayudante. El código de mi procedimiento almacenado para truncar todas las tablas es:

DROP PROCEDURE IF EXISTS trunctables;
DELIMITER ;;
CREATE  PROCEDURE trunctables(theDb varchar(64))
BEGIN
    declare tname varchar(64);
    declare tcursor CURSOR FOR 
    SELECT table_name FROM information_schema.tables WHERE table_type <> ''VIEW'' AND table_schema = theDb;
    SET FOREIGN_KEY_CHECKS = 0; 
    OPEN tcursor;
    l1: LOOP
        FETCH tcursor INTO tname;
        if tname = NULL then leave l1; end if;
        set @sql = CONCAT(''truncate `'', theDB, ''`.`'', tname, ''`'');
        PREPARE stmt from @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP l1;
    CLOSE tcursor;
    SET FOREIGN_KEY_CHECKS = 1; 
END ;;
DELIMITER ;

Una vez que tenga este procedimiento almacenado en su base de datos util, puede llamarlo como

call util.trunctables(''nameofdatabase'');

que ahora es exactamente una declaración :-)

Answer 5

Aquí hay un procedimiento que debe truncar todas las tablas en la base de datos local.

Déjame saber si no funciona y eliminaré esta respuesta.

No probado

CREATE PROCEDURE truncate_all_tables()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE cmd VARCHAR(2000);

   -- Declare the cursor
   DECLARE cmds CURSOR
   FOR
   SELECT CONCAT(''TRUNCATE TABLE '', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES;

   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE ''02000'' SET done=1;

   -- Open the cursor
   OPEN cmds;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH cmds INTO cmd;

      -- Execute the command
      PREPARE stmt FROM cmd;
      EXECUTE stmt;
      DROP PREPARE stmt;

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE cmds;

END;

Answer 6

Encontré esto para soltar todas las tablas en una base de datos:

mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | mysql -uUSERNAME -pPASSWORD DATABASENAME

Es útil si está limitado por la solución de alojamiento (no puede eliminar una base de datos completa).

Lo modifiqué para truncar las tablas. No hay "--add-truncate-table" para mysqldump, así que hice:

mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | sed -e ''s/DROP TABLE IF EXISTS/TRUNCATE TABLE/g'' | mysql -uUSERNAME -pPASSWORD DATABASENAME

funciona para mí - edita, corrigiendo un error tipográfico en el último comando

Answer 7

Esto imprimirá el comando para truncar todas las tablas:

SELECT GROUP_CONCAT(Concat(''TRUNCATE TABLE '',table_schema,''.'',TABLE_NAME) SEPARATOR '';'') FROM INFORMATION_SCHEMA.TABLES where table_schema in (''my_db'');

Answer 8

La siguiente consulta de MySQL producirá una sola consulta que truncará todas las tablas en una base de datos determinada. Se pasa por alto las teclas EXTRANJERAS:

SELECT CONCAT(
         ''SET FOREIGN_KEY_CHECKS=0; '',
         GROUP_CONCAT(dropTableSql SEPARATOR ''; ''), ''; '',
         ''SET FOREIGN_KEY_CHECKS=1;''
       ) as dropAllTablesSql
FROM   ( SELECT  Concat(''TRUNCATE TABLE '', table_schema, ''.'', TABLE_NAME) AS dropTableSql
         FROM    INFORMATION_SCHEMA.TABLES
         WHERE   table_schema = ''DATABASE_NAME'' ) as queries

Answer 9

MS SQL Server 2005+ (Eliminar IMPRIMIR para ejecución real ...)

EXEC sp_MSforeachtable ''PRINT ''''TRUNCATE TABLE ?''''''

Si su plataforma de base de datos admite vistas de INFORMATION_SCHEMA, tome los resultados de la siguiente consulta y ejecútelos.

SELECT ''TRUNCATE TABLE '' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

Prueba esto para MySQL:

SELECT Concat(''TRUNCATE TABLE '', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES

Agregar un punto y coma al Concat hace que sea más fácil de usar, por ejemplo, desde dentro de la mesa de trabajo mysql.

SELECT Concat(''TRUNCATE TABLE '', TABLE_NAME, '';'') FROM INFORMATION_SCHEMA.TABLES

Answer 10

Me parece que TRUNCATE TABLE ... tiene problemas con las restricciones de clave externa, incluso después de una RESTRICCIÓN DE TODO DE NOCHECK, por lo que en su lugar utilizo una instrucción DELETE FROM. Esto significa que las semillas de identidad no se restablecen, siempre puede agregar un DBCC CHECKIDENT para lograr esto.

I Use el siguiente código para imprimir en la ventana del mensaje el sql para truncar todas las tablas en la base de datos, antes de ejecutarlo. Solo hace que sea un poco más difícil cometer un error.

EXEC sp_MSforeachtable ''PRINT ''''ALTER TABLE ? NOCHECK CONSTRAINT ALL''''''
EXEC sp_MSforeachtable ''print ''''DELETE FROM ?''''''
EXEC sp_MSforeachtable ''print ''''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all''''''

Answer 11

Me pareció muy sencillo hacer algo como el código a continuación, simplemente reemplazar los nombres de las tablas con los suyos. importante asegúrese de que la última línea sea siempre SET FOREIGN_KEY_CHECKS = 1;

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `table1`;
TRUNCATE `table2`;
TRUNCATE `table3`;
TRUNCATE `table4`;
TRUNCATE `table5`;
TRUNCATE `table6`;
TRUNCATE `table7`;
SET FOREIGN_KEY_CHECKS=1;

Answer 12

No estoy seguro, pero creo que hay un comando mediante el cual puede copiar el esquema de la base de datos en una nueva base de datos, una vez que haya hecho esto, puede eliminar la base de datos anterior y luego puede copiar nuevamente el esquema de la base de datos al nombre anterior.

Answer 13

No. No hay un solo comando para truncar todas las tablas mysql a la vez. Tendrá que crear un pequeño script para truncar las tablas una por una.

ref: http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

Answer 14

Sé que este no es exactamente un comando, pero el resultado deseado se puede lograr desde phpMyAdmin siguiendo estos pasos:

1. Seleccionar (todas) las tablas que se eliminarán (Marcar todas)
2. Seleccione "Drop" / "Truncate" de la lista "With selected:"
3. En la página de confirmación ("¿Realmente desea:") copiar la consulta (todo con el fondo rojo)
4. Vaya a la parte superior y haga clic en SQL y escriba: "SET FOREIGN_KEY_CHECKS = 0;" luego pegar la consulta copiada previamente
5. Haga clic en "ir"

La idea es obtener rápidamente todas las tablas de la base de datos (lo que hace en 5 segundos y 2 clics), pero primero desactive las comprobaciones de clave externa. Sin CLI y sin quitar la base de datos y agregarla de nuevo.

Answer 15

Si usa el servidor SQL 2005, hay un procedimiento almacenado oculto que le permite ejecutar un comando o un conjunto de comandos en todas las tablas dentro de una base de datos. Aquí es cómo llamaría TRUNCATE TABLE con este procedimiento almacenado:

EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?"

Here hay un buen artículo que elabora más.

Para MySql, sin embargo, puede usar mysqldump y especificar las --add-drop-tables y --no-data para eliminar y crear todas las tablas ignorando los datos. Me gusta esto:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE]

Guía de uso de mysqldump de dev.mysql

Answer 16

Soln 1)

mysql> select group_concat(''truncate'','' '',table_name,'';'') from information_schema.tables where table_schema="db_name" into outfile ''/tmp/a.txt'';
mysql> /tmp/a.txt;

Soln 2)

- Export only structure of a db
- drop the database
- import the .sql of structure 

- editar ----

earlier in solution 1, i had mentioned concat() instead of group_concat() which would have not returned the desired result

Answer 17

Truncar múltiples tablas de bases de datos en la instancia de MySQL

SELECT Concat(''TRUNCATE TABLE '',table_schema,''.'',TABLE_NAME, '';'') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in (''db1_name'',''db2_name'');

Utilice el resultado de la consulta para truncar tablas

Nota: puede ser que obtendrá este error:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Esto sucede si hay tablas con referencias de claves foráneas a la tabla que intenta eliminar / truncar.

Antes de truncar tablas Todo lo que necesitas hacer es:

SET FOREIGN_KEY_CHECKS=0;

Trunca tus tablas y vuelve a cambiarlas a

SET FOREIGN_KEY_CHECKS=1;

Answer 18

Usa esto y forma la consulta.

SELECT Concat(''TRUNCATE TABLE '',table_schema,''.'',TABLE_NAME, '';'') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in (db1,db2)
INTO OUTFILE ''/path/to/file.sql'';

Ahora usa esto para usar esta consulta

mysql -u username -p </path/to/file.sql

si obtienes un error como este

ERROR 1701 (42000) at line 3: Cannot truncate a table referenced in a foreign key constraint

la forma más fácil de hacerlo es en la parte superior de su archivo, agregue esta línea

SET FOREIGN_KEY_CHECKS=0;

que dice que no queremos comprobar las restricciones de clave foránea mientras revisamos este archivo.

Truncará todas las tablas en las bases de datos db1 y bd2.

Answer 19

Utilice phpMyAdmin de esta manera:

Vista de la base de datos => Comprobar todo (tablas) => Vaciar

Si desea ignorar las comprobaciones de clave externa, puede desmarcar la casilla que dice:

[] Habilitar verificaciones de clave foránea

Deberá estar ejecutando al menos la versión 4.5.0 o superior para obtener esta casilla de verificación.

No es MySQL CLI-fu, pero bueno, ¡funciona!

Answer 20

aquí porque sé aquí

   SELECT Concat(''TRUNCATE TABLE '',table_schema,''.'',TABLE_NAME, '';'') 
    FROM INFORMATION_SCHEMA.TABLES where  table_schema in (''databasename1'',''databasename2'');

Si no se puede eliminar o actualizar una fila principal: una restricción de clave externa falla

Esto sucede si hay tablas con referencias de claves foráneas a la tabla que intenta eliminar / truncar.

Antes de truncar tablas Todo lo que necesitas hacer es:

SET FOREIGN_KEY_CHECKS=0;

Trunca tus tablas y vuelve a cambiarlas a

SET FOREIGN_KEY_CHECKS=1; 

usuario este código php

    $truncate = mysql_query("SELECT Concat(''TRUNCATE TABLE '',table_schema,''.'',TABLE_NAME, '';'') as tables_query FROM INFORMATION_SCHEMA.TABLES where table_schema in (''databasename'')");

    while($truncateRow=mysql_fetch_assoc($truncate)){

        mysql_query($truncateRow[''tables_query'']);

    }
?>

Ver detalle aquí link

Answer 21

<?php
// connect to database
$conn=mysqli_connect("localhost","user","password","database");

// check connection
if (mysqli_connect_errno()) {
  exit(''Connect failed: ''. mysqli_connect_error());
}

// sql query
$sql =mysqli_query($conn,"TRUNCATE " . TABLE_NAME);


// Print message
if ($sql === TRUE) {
  echo ''data delete successfully'';
}
else {
 echo ''Error: ''. $conn->error;
}

$conn->close();

?>

Aquí está el fragmento de código que utilizo para borrar una tabla. Solo cambia la información de $ conn y TABLE_NAME.

Answer 22

SET FOREIGN_KEY_CHECKS = 0;

SELECT @str := CONCAT(''TRUNCATE TABLE '', table_schema, ''.'', table_name, '';'')
FROM   information_schema.tables
WHERE  table_type   = ''BASE TABLE''
  AND  table_schema IN (''db1_name'',''db2_name'');

PREPARE stmt FROM @str;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

Answer 23

TB=$( mysql -Bse "show tables from DATABASE" );
for i in ${TB};
    do echo "Truncating table ${i}";
    mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table DATABASE.${i}; set foreign_key_checks=1; set unique_checks=1";
    sleep 1;
done

-

David,

Gracias por tomarse el tiempo para formatear el código, pero así es como se debe aplicar.

-Kurt

En un cuadro de UNIX o Linux:

Asegúrese de que está en un shell bash. Estos comandos deben ejecutarse desde la línea de comandos de la siguiente manera.

Nota:

Almaceno mis credenciales en mi archivo ~ / .my.cnf, por lo que no necesito proporcionarlas en la línea de comandos.

Nota:

cpm es el nombre de la base de datos

Solo muestro una pequeña muestra de los resultados, de cada comando.

Encuentra tus restricciones de clave externa:

klarsen@Chaos:~$ mysql -Bse "select concat(table_name, '' depends on '', referenced_table_name)
             from information_schema.referential_constraints
             where constraint_schema = ''cpm''
             order by referenced_table_name"

1. aprobación_sistema_externo depende de aprobación_request
2. la dirección depende del cliente
3. identificación del cliente depende del cliente
4. external_id depende del cliente
5. credencial depende del cliente
6. email_address depende del cliente
7. La petición de aprobación depende del cliente.
8. customer_status depende del cliente
9. customer_image depende del cliente

Listar las tablas y los conteos de filas:

klarsen@Chaos:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''cpm''" | cat -n

 1  address 297
 2  approval_external_system    0
 3  approval_request    0
 4  country 189
 5  credential  468
 6  customer    6776
 7  customer_identification 5631
 8  customer_image  2
 9  customer_status 13639

Trunca tus tablas:

klarsen@Chaos:~$ TB=$( mysql -Bse "show tables from cpm" ); for i in ${TB}; do echo "Truncating table ${i}"; mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table cpm.${i}; set foreign_key_checks=1; set unique_checks=1"; sleep 1; done

1. Dirección de tabla truncada
2. Tabla truncada aprobación_sistema_externo
3. Tabla de truncamiento
4. País de mesa truncado
5. Credencial de mesa truncada
6. Mesa de truncamiento al cliente.
7. Tabla de truncamiento identificación de clientes
8. Tabla de truncamiento customer_image
9. Tabla de truncamiento customer_status

Verifique que funcionó:

klarsen@Chaos:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''cpm''" | cat -n

 1  address 0
 2  approval_external_system    0
 3  approval_request    0
 4  country 0
 5  credential  0
 6  customer    0
 7  customer_identification 0
 8  customer_image  0
 9  customer_status 0
10  email_address   0

En una caja de Windows:

NOTA:

cpm es el nombre de la base de datos

C:/>for /F "tokens=*" %a IN (''mysql -Bse "show tables" cpm'') do mysql -e "set foreign_key_checks=0; set unique_checks=0; truncate table %a; foreign_key_checks=1; set unique_checks=1" cpm

Answer 24

mysqldump -u root -p --no-data dbname > schema.sql
mysqldump -u root -p drop dbname
mysqldump -u root -p < schema.sql