是否有一个很好的简单的方法来删除MySQL数据库中的所有表,忽略任何外键约束,可能在那里?
当前回答
目前为止对我来说最好的解决方案
选择数据库->右键单击->任务->生成脚本-将打开生成脚本的向导。在set Scripting选项中选择对象后,单击高级按钮。在“脚本删除和创建”下选择脚本删除。
运行脚本。
其他回答
下面是修改后的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`;
你可以:
select concat('drop table if exists ', table_name, ' cascade;')
from information_schema.tables;
然后运行生成的查询。它们将删除当前数据库中的每一个表。
下面是删除表命令的一些帮助。
只是一个温馨的提醒,
如果可能并且没有其他问题,您可以删除数据库并重新创建它。
删除数据库<database_name> 创建数据库<database_name>
如果在linux(或任何其他支持管道、echo和grep的系统)中,你可以用一行代码完成:
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;
我知道这是一个老问题,但我认为这个方法又快又简单。
简单明了(可能)。
也许不是一个很好的解决方案,但这对我很有效,帮了我大忙。
服务版本:5.6.38 MySQL Community Server (GPL)
我遵循的步骤:
1. generate drop query using concat and group_concat.
2. use database
3. disable key constraint check
4. copy the query generated from step 1
5. enable key constraint check
6. run show table
MySQL壳
mysql> SYSTEM CLEAR;
mysql> SELECT CONCAT('DROP TABLE IF EXISTS `', GROUP_CONCAT(table_name SEPARATOR '`, `'), '`;') AS dropquery FROM information_schema.tables WHERE table_schema = 'emall_duplicate';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dropquery |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`; |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> USE emall_duplicate;
Database changed
mysql> SET FOREIGN_KEY_CHECKS = 0; Query OK, 0 rows affected (0.00 sec)
// copy and paste generated query from step 1
mysql> DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`;
Query OK, 0 rows affected (0.18 sec)
mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW tables;
Empty set (0.01 sec)
mysql>