MySQL ELIMINAR todas las tablas, ignorando las claves foráneas
¿Hay una forma fácil y agradable de eliminar todas las tablas de una base de datos MySQL, ignorando cualquier restricción de clave externa que pueda haber allí?
21 answers
Encontré útil el conjunto generado de sentencias drop, y recomiendo estos ajustes:
- Limita las gotas generadas a tu base de datos de esta manera:
SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';
Nota: Esto no ejecuta las sentencias DROP, solo le da una lista de ellas. Tendrá que cortar y pegar la salida en su motor SQL para ejecutarlos.
- Nota, por http://dev.mysql.com/doc/refman/5.5/en/drop-table.html , caer con cascada no tiene sentido / engañoso:
"RESTRICT y CASCADE están permitidos para facilitar la portabilidad. En MySQL 5.5, no hacen nada."
Por lo tanto, para que las instrucciones drop funcionen si necesita:
SET FOREIGN_KEY_CHECKS = 0
Esto deshabilitará las comprobaciones de integridad referencial, por lo que cuando haya terminado de realizar las caídas que necesita, querrá restablecer la comprobación de claves con
SET FOREIGN_KEY_CHECKS = 1
- La ejecución final debería ser como:
SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;
NB: para usar la salida de SELECT más fácil, la opción mysql-B puede ayudar.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-04-26 16:56:53
De http://www.devdaily.com/blog/post/mysql/drop-mysql-tables-in-any-order-foreign-keys:
SET FOREIGN_KEY_CHECKS = 0;
drop table if exists customers;
drop table if exists orders;
drop table if exists order_details;
SET FOREIGN_KEY_CHECKS = 1;
(Tenga en cuenta que esto responde a cómo deshabilitar las comprobaciones de claves foráneas para poder colocar las tablas en orden arbitrario. No responde cómo generar automáticamente sentencias drop-table para todas las tablas existentes y ejecutarlas en un solo script. La respuesta de Jean lo hace.)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-08-18 06:19:05
Aquí está el procedimiento almacenado de SurlyDre modificado para que las claves foráneas sean ignoradas:
DROP PROCEDURE IF EXISTS `drop_all_tables`;
DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
DECLARE _done INT DEFAULT FALSE;
DECLARE _tableName VARCHAR(255);
DECLARE _cursor CURSOR FOR
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = SCHEMA();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
SET FOREIGN_KEY_CHECKS = 0;
OPEN _cursor;
REPEAT FETCH _cursor INTO _tableName;
IF NOT _done THEN
SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
PREPARE stmt1 FROM @stmt_sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
UNTIL _done END REPEAT;
CLOSE _cursor;
SET FOREIGN_KEY_CHECKS = 1;
END$$
DELIMITER ;
call drop_all_tables();
DROP PROCEDURE IF EXISTS `drop_all_tables`;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-22 08:22:51
De esta respuesta,
Ejecutar:
use `dbName`; --your db name here
SET FOREIGN_KEY_CHECKS = 0;
SET @tables = NULL;
SET GROUP_CONCAT_MAX_LEN=32768;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables, '') INTO @tables;
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
Esto elimina las tablas de la base de datos actualmente en uso. Puede establecer la base de datos actual usando use
.
O de lo contrario, la respuesta aceptada de Dion es más simple, excepto que necesita ejecutarla dos veces, primero para obtener la consulta y segundo para ejecutar la consulta. Proporcioné algunas tontas marcas de retroceso para escapar de los caracteres especiales en los nombres de bases de datos y tablas.
SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'dbName'; --your db name here
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:26:36
Cada enfoque anterior incluye mucho más trabajo que este AFAICT...
( mysqldump --add-drop-table --no-data -u root -p database | grep 'DROP TABLE' ) > ./drop_all_tables.sql
mysql -u root -p database < ./drop_all_tables.sql
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-01-15 19:59:47
Aquí hay una solución basada en el cursor. Un poco largo, pero funciona como un solo lote SQL:
DROP PROCEDURE IF EXISTS `drop_all_tables`;
DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
DECLARE _done INT DEFAULT FALSE;
DECLARE _tableName VARCHAR(255);
DECLARE _cursor CURSOR FOR
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = SCHEMA();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
OPEN _cursor;
REPEAT FETCH _cursor INTO _tableName;
IF NOT _done THEN
SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
PREPARE stmt1 FROM @stmt_sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
UNTIL _done END REPEAT;
CLOSE _cursor;
END$$
DELIMITER ;
call drop_all_tables();
DROP PROCEDURE IF EXISTS `drop_all_tables`;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-04-15 07:00:24
Puedes hacer:
select concat('drop table if exists ', table_name, ' cascade;')
from information_schema.tables;
Luego ejecute las consultas generadas. Eliminarán cada tabla de la base de datos actual.
Aquí hay algo de ayuda en el comando drop table
.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-08-13 12:40:13
Se me ocurrió esta modificación en la respuesta de Dion Truter para hacerlo más fácil con muchas tablas:
SET GROUP_CONCAT_MAX_LEN = 10000000;
SELECT CONCAT('SET FOREIGN_KEY_CHECKS=0;\n',
GROUP_CONCAT(CONCAT('DROP TABLE IF EXISTS `', table_name, '`')
SEPARATOR ';\n'),
';\nSET FOREIGN_KEY_CHECKS=1;')
FROM information_schema.tables
WHERE table_schema = 'SchemaName';
Esto devuelve todo en un campo, por lo que puede copiar una vez y eliminar todas las tablas (use Copy Field Content (unquoted)
en Workbench). Si tiene MUCHAS tablas, puede alcanzar algunos límites en GROUP_CONCAT()
. Si es así, aumente la variable max len (y max_allowed_packet
, si es necesario).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-07-24 01:18:34
Aquí hay una forma automatizada de hacer esto a través de un script bash:
host=$1
dbName=$2
user=$3
password=$4
if [ -z "$1" ]
then
host="localhost"
fi
# drop all the tables in the database
for i in `mysql -u$user -p$password $dbName -e "show tables" | grep -v Tables_in` ; do echo $i && mysql -u$user -p$password $dbName -e "SET FOREIGN_KEY_CHECKS = 0; drop table $i ; SET FOREIGN_KEY_CHECKS = 1" ; done
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-04-09 20:33:09
Si está en linux (o cualquier otro sistema que soporte piping, echo y grep) puede hacerlo con una línea:
echo "SET FOREIGN_KEY_CHECKS = 0;" > temp.txt; \
mysqldump -u[USER] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP >> temp.txt; \
echo "SET FOREIGN_KEY_CHECKS = 1;" >> temp.txt; \
mysql -u[USER] -p[PASSWORD] [DATABASE] < temp.txt;
Sé que esta es una vieja pregunta, pero creo que este método es rápido y simple.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-11-11 18:17:48
Una línea para eliminar todas las tablas de una base de datos dada:
echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"
Al ejecutar esto, se eliminarán todas las tablas de la base de datos DATABASE_NAME.
Y lo bueno de esto es que el nombre de la base de datos solo se escribe explícitamente una vez.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-01 12:24:13
En php es tan fácil como:
$pdo = new PDO('mysql:dbname=YOURDB', 'root', 'root');
$pdo->exec('SET FOREIGN_KEY_CHECKS = 0');
$query = "SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'YOURDB'";
foreach($pdo->query($query) as $row) {
$pdo->exec($row[0]);
}
$pdo->exec('SET FOREIGN_KEY_CHECKS = 1');
Solo recuerde cambiar YOURDB al nombre de su base de datos, y obviamente el usuario/pase.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-08-21 13:38:26
En un shell de Linux como bash / zsh:
DATABASE_TO_EMPTY="your_db_name";
{ echo "SET FOREIGN_KEY_CHECKS = 0;" ; \
mysql "$DATABASE_TO_EMPTY" --skip-column-names -e \
"SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') \
FROM information_schema.tables WHERE table_schema = '$DATABASE_TO_EMPTY';";\
} | mysql "$DATABASE_TO_EMPTY"
Esto generará los comandos, luego los canalizará inmediatamente a una 2da instancia de cliente que eliminará las tablas.
La parte inteligente, por supuesto, se copia de otras respuestas aquí - yo solo quería una copia y pasteable de una sola línea (ish) para realmente hacer el trabajo que el OP quería.
Nota por supuesto, también tendrá que poner sus credenciales (dos veces) en estos comandos mysql, a menos que tenga una seguridad muy baja configuración. (o podría alias su comando mysql para incluir sus credenciales.)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-07-08 10:27:58
Googlear el tema siempre me lleva a esta pregunta, así que aquí está trabajando el código mysql que elimina TANTO las tablas como las vistas:
DROP PROCEDURE IF EXISTS `drop_all_tables`;
DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
DECLARE _done INT DEFAULT FALSE;
DECLARE _tableName VARCHAR(255);
DECLARE _cursor CURSOR FOR
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = SCHEMA();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
SET FOREIGN_KEY_CHECKS = 0;
OPEN _cursor;
REPEAT FETCH _cursor INTO _tableName;
IF NOT _done THEN
SET @stmt_sql1 = CONCAT('DROP TABLE IF EXISTS ', _tableName);
SET @stmt_sql2 = CONCAT('DROP VIEW IF EXISTS ', _tableName);
PREPARE stmt1 FROM @stmt_sql1;
PREPARE stmt2 FROM @stmt_sql2;
EXECUTE stmt1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;
END IF;
UNTIL _done END REPEAT;
CLOSE _cursor;
SET FOREIGN_KEY_CHECKS = 1;
END$$
DELIMITER ;
call drop_all_tables();
DROP PROCEDURE IF EXISTS `drop_all_tables`;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-15 12:29:25
Basándose en la respuesta de @Dion Truter y @Wade Williams, el siguiente script de shell eliminará todas las tablas, después de mostrar primero lo que está a punto de ejecutarse, y darle la oportunidad de abortar usando Ctrl-C.
#!/bin/bash
DB_HOST=xxx
DB_USERNAME=xxx
DB_PASSWORD=xxx
DB_NAME=xxx
CMD="mysql -sN -h ${DB_HOST} -u ${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME}"
# Generate the drop statements
TMPFILE=/tmp/drop-${RANDOM}.sql
echo 'SET FOREIGN_KEY_CHECKS = 0;' > ${TMPFILE}
${CMD} $@ >> ${TMPFILE} << ENDD
SELECT concat('DROP TABLE IF EXISTS \`', table_name, '\`;')
FROM information_schema.tables
WHERE table_schema = '${DB_NAME}';
ENDD
echo 'SET FOREIGN_KEY_CHECKS = 1;' >> ${TMPFILE}
# Warn what we are about to do
echo
cat ${TMPFILE}
echo
echo "Press ENTER to proceed (or Ctrl-C to abort)."
read
# Run the SQL
echo "Dropping tables..."
${CMD} $@ < ${TMPFILE}
echo "Exit status is ${?}."
rm ${TMPFILE}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-03-07 02:05:00
Solo pon aquí algún comentario útil hecho por Jonathan Watt para eliminar todas las tablas
MYSQL="mysql -h HOST -u USERNAME -pPASSWORD DB_NAME"
$MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; drop table `" $1 "`;"}' | $MYSQL
unset MYSQL
Me ayuda y espero que pueda ser útil
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-06-24 11:14:11
Esta es una publicación bastante antigua, pero ninguna de las respuestas aquí realmente respondió a la pregunta en mi opinión, así que espero que mi publicación ayude a la gente!
Encontré esta solución en otra pregunta que funciona muy bien para mí:
mysql -Nse 'show tables' DB_NAME | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; truncate table \`$table\`" DB_NAME; done
Eso realmente vaciará todas sus tablas en la base de datos DB_NAME
, y no solo mostrará la línea de comandos TRUNCATE
.
Espero que esto ayude!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-08-20 14:07:59
Esta solución se basa en la respuesta @SkyLeach pero con el soporte de tablas de caída con claves foráneas.
echo "SET FOREIGN_KEY_CHECKS = 0;" > ./drop_all_tables.sql
mysqldump --add-drop-table --no-data -u user -p dbname | grep 'DROP TABLE' >> ./drop_all_tables.sql
echo "SET FOREIGN_KEY_CHECKS = 1;" >> ./drop_all_tables.sql
mysql -u user -p dbname < ./drop_all_tables.sql
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-04-04 15:15:16
DB="your database name" \
&& mysql $DB < "SET FOREIGN_KEY_CHECKS=0" \
&& mysqldump --add-drop-table --no-data $DB | grep 'DROP TABLE' | grep -Ev "^$" | mysql $DB \
&& mysql $DB < "SET FOREIGN_KEY_CHECKS=1"
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-09-14 09:28:36
Utilizo lo siguiente con un servidor MSSQL:
if (DB_NAME() = 'YOUR_DATABASE')
begin
while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
exec (@sql)
PRINT @sql
end
while(exists(select 1 from INFORMATION_SCHEMA.TABLES))
begin
declare @sql2 nvarchar(2000)
SELECT TOP 1 @sql2=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLES
exec (@sql2)
PRINT @sql2
end
end
else
print('Only run this script on the development server!!!!')
Reemplace SU_DATABASE con el nombre de su base de datos o elimine toda la instrucción IF (me gusta la seguridad añadida).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-02-23 09:05:56
La mejor solución para mí hasta ahora
Seleccione Base de datos -> Clic derecho -> Tareas -> Generar scripts - se abrirá el asistente para generar scripts. Después de elegir objetos en la opción set Scripting, haga clic en Botón Avanzado. En "Script DROP and CREATE" seleccione Script DROP.
Ejecutar script.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-03-17 14:08:43