MySQL DROP 所有表，忽略外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3476765/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL DROP all tables, ignoring foreign keys
提问by bcmcfc
Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?
有没有一种简单的方法可以从 MySQL 数据库中删除所有表，而忽略其中可能存在的任何外键约束？
回答by Dion Truter
I found the generated set of drop statements useful, and recommend these tweaks:
我发现生成的 drop 语句集很有用，并推荐这些调整：
- Limit the generated drops to your database like this:
- 将生成的 drop 限制到您的数据库中，如下所示：
SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';
Note 1: This does not execute the DROP statements, it just gives you a list of them.You will need to cut and paste the output into your SQL engine to execute them.
注意 1：这不会执行 DROP 语句，它只是为您提供它们的列表。您需要将输出剪切并粘贴到 SQL 引擎中以执行它们。
Note 2: If you have VIEWs, you'll have to correct each DROP TABLE `VIEW_NAME`statement to DROP VIEW `VIEW_NAME`manually.
注意 2：如果您有 VIEW，则必须手动更正每个DROP TABLE `VIEW_NAME`语句DROP VIEW `VIEW_NAME`。
- Note, per http://dev.mysql.com/doc/refman/5.5/en/drop-table.html, dropping with cascade is pointless / misleading:
- 请注意，根据http://dev.mysql.com/doc/refman/5.5/en/drop-table.html，使用级联删除毫无意义/误导：
"RESTRICT and CASCADE are permitted to make porting easier. In MySQL 5.5, they do nothing."
“允许 RESTRICT 和 CASCADE 使移植更容易。在 MySQL 5.5 中，它们什么都不做。”
Therefore, in order for the drop statements to work if you need:
因此，如果您需要，为了使 drop 语句起作用：
SET FOREIGN_KEY_CHECKS = 0
This will disable referential integrity checks - so when you are done performing the drops you need, you will want to reset key checking with
这将禁用参照完整性检查 - 因此，当您完成所需的删除操作后，您将需要使用以下命令重置密钥检查
SET FOREIGN_KEY_CHECKS = 1
- The final execution should look like:
- 最终执行应如下所示：
SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;
NB: to use output of SELECT easier, mysql -B option can help.
注意：为了更容易地使用 SELECT 的输出，mysql -B 选项可以提供帮助。
回答by chiccodoro
From http://www.devdaily.com/blog/post/mysql/drop-mysql-tables-in-any-order-foreign-keys:
来自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;
(Note that this answers how to disable foreign key checks in order to be able to drop the tables in arbitrary order. It does not answer how to automatically generate drop-table statements for all existing tables and execute them in a single script. Jean's answerdoes.)
（请注意，这回答了如何禁用外键检查以便能够以任意顺序删除表。它不回答如何为所有现有表自动生成删除表语句并在单个脚本中执行它们。Jean 的回答做。）
回答by Jean-Fran?ois Beauchamp
Here is SurlyDre's stored procedure modified so that foreign keys are ignored:
这是 SurlyDre 的存储过程修改后的外键被忽略：
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`;
回答by SkyLeach
every approach above includes a lot more work than this one AFAICT...
上面的每一种方法都比这个 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
回答by nawfal
From this answer,
从这个答案，
execute:
执行：
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;
This drops tables from the database currently in use. You can set current database using use.
这会从当前使用的数据库中删除表。您可以使用use.
Or otherwise, Dion's accepted answer is simpler, except you need to execute it twice, first to get the query, and second to execute the query. I provided some silly back-ticks to escape special characters in db and table names.
否则，Dion 接受的答案更简单，除了您需要执行两次，第一次获取查询，第二次执行查询。我提供了一些愚蠢的反引号来转义数据库和表名中的特殊字符。
SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'dbName'; --your db name here
回答by SurlyDre
Here's a cursor based solution. Kinda lengthy but works as a single SQL batch:
这是一个基于游标的解决方案。有点冗长，但可以作为单个 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`;
回答by Pablo Santa Cruz
回答by Ryan P
I came up with this modification on Dion Truter's answer to make it easier with many tables:
我对 Dion Truter 的回答进行了修改，以便更轻松地处理许多表格：
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';
This returns the entire thing in one field, so you can copy once and delete all the tables (use Copy Field Content (unquoted)in Workbench). If you have a LOT of tables, you may hit some limits on GROUP_CONCAT(). If so, increase the max len variable (and max_allowed_packet, if necessary).
这会在一个字段中返回整个内容，因此您可以复制一次并删除所有表（Copy Field Content (unquoted)在 Workbench 中使用）。如果你有很多表，你可能会遇到一些限制GROUP_CONCAT()。如果是这样，请增加 max len 变量（以及max_allowed_packet，如有必要）。
回答by Sergey Alaev
Googling on topic always brings me to this SO question so here is working mysql code that deletes BOTH tables and views:
谷歌搜索主题总是让我想到这个 SO 问题，所以这里是删除两个表和视图的工作 mysql 代码：
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`;
回答by mgershen
A one liner to drop all tables from a given database:
从给定数据库中删除所有表的单行：
echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"
Running this will drop all tables from database DATABASE_NAME.
运行此命令将从数据库 DATABASE_NAME 中删除所有表。
And a nice thing about this is that the database name is only written explicitly once.
这样做的一个好处是数据库名称只显式写入一次。
