MySQL DROP所有表，忽略外键

从# EYZ0:

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;

(注意，这回答了如何禁用外键检查，以便能够以任意顺序删除表。它没有回答如何为所有现有的表自动生成drop-table语句，并在一个脚本中执行它们。# EYZ0。)

你可以:

select concat('drop table if exists ', table_name, ' cascade;')
from information_schema.tables;

然后运行生成的查询。它们将删除当前数据库中的每一个表。

在这里是drop table命令的一些帮助。

我发现生成的drop语句集很有用，并推荐以下调整:

1. 将生成的drop限制在数据库中，如下所示:

SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';

您需要将输出剪切并粘贴到SQL引擎中才能执行它们。

注2:如果你有视图，你必须手动将每个DROP TABLE `VIEW_NAME`语句纠正为DROP VIEW `VIEW_NAME`。

2. 注意，根据http://dev.mysql.com/doc/refman/5.5/en/drop-table.html，级联删除是没有意义的/误导:

允许RESTRICT和CASCADE使移植更容易。在MySQL 5.5中，它们什么都不做。”

因此，为了让drop语句工作，如果你需要:

SET FOREIGN_KEY_CHECKS = 0

这将禁用参考完整性检查-所以当你完成执行你需要的下降，你会想要重置键检查

SET FOREIGN_KEY_CHECKS = 1

3. 最终的执行应该是这样的:

SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;

注意:要使用SELECT输出更容易，mysql -B选项可以帮助。

这是一个基于游标的解决方案。有点长，但可以作为单个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`;

下面是修改后的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`;

下面是一种通过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 -h$host -u$user -p$password $dbName -e "show tables" | grep -v Tables_in` ; do  echo $i && mysql -h$host -u$user -p$password $dbName -e "SET FOREIGN_KEY_CHECKS = 0; drop table $i ; SET FOREIGN_KEY_CHECKS = 1" ; done

我对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';

这将在一个字段中返回全部内容，因此您可以复制一次并删除所有表(在Workbench中使用Copy Field Content (unquoted))。如果你有很多表，你可能会在GROUP_CONCAT()上遇到一些限制。如果是这样，增加max len变量(如果必要，还有max_allowed_packet)。

在php中，它很简单:

$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');

只需要记住将YOURDB更改为数据库的名称，当然还有user/pass。

从# EYZ0,

执行:

  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;

这将从当前使用的数据库中删除表。您可以使用use设置当前数据库。

或者，Dion接受的答案更简单，只是需要执行两次，第一次获取查询，第二次执行查询。我提供了一些愚蠢的反勾来转义db和表名中的特殊字符。

  SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
FROM   information_schema.tables
WHERE  table_schema = 'dbName'; --your db name here

在像bash/zsh这样的Linux shell中:

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"

这将生成命令，然后立即将它们输送到第二个客户端实例，该实例将删除表。

聪明的一点当然是从其他答案复制在这里-我只是想要一个复制和粘贴一行(ish)实际上做 OP想要的工作。

请注意当然你也必须在这些mysql命令中输入你的凭证(两次)，除非你有一个非常低的安全设置。(或者你可以用mysql命令的别名来包含你的学分。)

如果在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;

我知道这是一个老问题，但我认为这个方法又快又简单。

我使用以下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!!!!')

用数据库的名称替换YOUR_DATABASE或删除整个IF语句(我喜欢增加的安全性)。

这是一个相当老的帖子，但这里没有一个答案真正回答了我的问题，所以我希望我的帖子能帮助到人们!

我在另一个问题上找到了一个非常适合我的解决方案:

mysql -Nse 'show tables' DB_NAME | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; truncate table \`$table\`" DB_NAME; done

这实际上会清空数据库DB_NAME中的所有表，而不仅仅是显示TRUNCATE命令行。

希望这能有所帮助!

上面的每个方法都包含了比这个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

最好的解决方案到目前为止

选择数据库->右键单击->任务->生成脚本 -将打开生成脚本的向导。在set Scripting选项中选择对象后，单击先进的按钮。在“脚本删除和创建”下选择脚本下降。

运行脚本。

从给定数据库中删除所有表的一行代码:

echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"

运行此命令将删除数据库DATABASE_NAME中的所有表。

这样做的一个好处是，数据库名称只显式地写入一次。

基于@Dion Truter和@Wade Williams的回答，下面的shell脚本将删除所有表，首先显示它将要运行的内容，并让您有机会使用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}

谷歌主题总是把我带到这个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`;

此解决方案基于@SkyLeach答案，但支持删除带外键的表。

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

只需在这里添加一些有用的乔纳森·瓦特的评论来删除所有表

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

这对我有帮助，我希望它能有用

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"

# EYZ0

也许不是一个很好的解决方案，但这对我很有效，帮了我大忙。

服务版本: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>

从命令行中删除所有的表:

mysqldump -u [user_name] -p[password] -h [host_name] --add-drop-table --no-data [database_name] | grep ^DROP | mysql -u [user_name] -p[password] -h [host_name] [database_name]

其中[user_name]、[password]、[host_name]和[database_name]需要替换为真实的数据(用户、密码、主机名、数据库名)。

不从SQL复制返回值的一步解决方案选择使用过程的查询。

SET FOREIGN_KEY_CHECKS = 0;
SET SESSION group_concat_max_len = 1000000;


SET @TABLES = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name,'`') INTO @TABLES FROM information_schema.tables
WHERE table_schema = 'databaseName';


SET @TABLES = CONCAT('DROP TABLE IF EXISTS ', @TABLES);


PREPARE stmt FROM @TABLES;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


SET SESSION group_concat_max_len = 1024;
SET FOREIGN_KEY_CHECKS = 1

所有人都给出了很好的答案，但是，我为熟悉电子表格/excel表格的用户提供了另一种选择。根据第一个解决方案，我们得到了一个命令列表，但我们仍然需要截断第一个和最后一个字符('|')

1. 用“< p >; # EYZ0"查询将得到所有表的列表;

2. 现在复制结果并粘贴到excel表格中(假设所有记录都在excel的“A”列中)

3. 首先，您需要删除第一个和最后一个'|'符号函数来删除第一个字符ie。“|”

   =正确(A1, LEN (A1) 1)

函数删除最后一个字符。'|'并添加一个结尾分号

=CONCAT(LEFT(B1,LEN(B1)-1),";")

4. 现在使用CONCAT函数创建最终的查询列表

   =CONCAT("drop table "，C1)

只是一个温馨的提醒，

如果可能&如果没有其他问题，您可以删除数据库并重新创建它。

• Drop database <
• 创建数据库