MySQL DROP所有表，忽略外键-Java 学习之路

304

是否有一个很好的简单方法从MySQL数据库中删除所有表，忽略可能存在的任何外键约束？

21 回答

在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更改为数据库的名称，显然是用户/传递 .

回复于 2024-04-28T06:40:07+08:00

0
我发现生成的drop语句集很有用，并建议这些调整：
- 将生成的drop限制为数据库，如下所示：
```
SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';
```
Note: This does not execute the DROP statements, it just gives you a list of them. 您需要将输出剪切并粘贴到SQL引擎中以执行它们 .
- 注意，按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
```
- 最终执行应如下所示：
```
SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;
```
注意：使用SELECT的输出更容易，mysql -B选项可以提供帮助 .
回复于 2024-04-28T06:40:07+08:00

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

我知道这是一个老问题，但我认为这种方法快速而简单 .

回复于 2024-04-28T06:40:07+08:00

谷歌搜索主题总是带我到这个问题所以这里是工作mysql代码删除BOTH表和视图：

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`;

回复于 2024-04-28T06:40:07+08:00

23
来自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;
```
（请注意，这解释了如何禁用外键检查以便能够以任意顺序删除表 . 它没有回答如何为所有现有表自动生成drop-table语句并在单个脚本中执行它们. Jean's answer . ）
回复于 2024-04-28T06:40:07+08:00

以下是通过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

回复于 2024-04-28T06:40:07+08:00

2
在像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想要的工作 .

Note 当然，除非你的安全设置非常低，否则你必须将你的凭证放入（两次）这些mysql命令中 . （或者你可以用你的mysql命令别名来包含你的信用 . ）
回复于 2024-04-28T06:40:07+08:00

348

在@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}

回复于 2024-04-28T06:40:07+08:00

2

到目前为止，对我来说是 Best solution

选择 Database -> Right Click -> Tasks -> Generate Scripts - 将打开向导以生成脚本 . 在set Scripting选项中选择对象后，单击 Advanced Button . 在 "Script DROP and CREATE" 下选择 Script DROP .

运行脚本 .

回复于 2024-04-28T06:40:07+08:00
-6
这是一篇非常古老的帖子，但是我认为这里的答案都没有真正回答这个问题，所以我希望我的帖子能帮助别人！

我在另一个对我有用的问题上找到了这个解决方案：
```
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 命令行 .

希望这可以帮助！
回复于 2024-04-28T06:40:07+08:00

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"

回复于 2024-04-28T06:40:07+08:00

-1
你可以做：
```
select concat('drop table if exists ', table_name, ' cascade;')
  from information_schema.tables;
```
然后运行生成的查询 . 它们将删除当前数据库上的每个表 .

Here是对 drop table 命令的一些帮助 .
回复于 2024-04-28T06:40:07+08:00

只需在这里放一些有用的comment made by Jonathan Watt来删除所有表格

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

它帮助我，我希望它可能有用

回复于 2024-04-28T06:40:07+08:00

来自this answer，

执行：

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接受的答案更简单 . 我提供了一些愚蠢的反向标记来逃避数据库和表名中的特殊字符 .

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

回复于 2024-04-28T06:40:07+08:00

120

这里修改了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`;

回复于 2024-04-28T06:40:07+08:00

我在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语句（我喜欢增加的安全性） .

回复于 2024-04-28T06:40:07+08:00

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

回复于 2024-04-28T06:40:07+08:00

9
从给定数据库中删除所有表的一个内容：
```
echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"
```
运行此命令将删除数据库DATABASE_NAME中的所有表 .

关于这一点的一个好处是数据库名称只显式写入一次 .
回复于 2024-04-28T06:40:07+08:00

此解决方案基于@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

回复于 2024-04-28T06:40:07+08:00

上述每种方法都包含比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

回复于 2024-04-28T06:40:07+08:00

79
我在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() 上达到一些限制 . 如果所以，增加最大len变量（和 max_allowed_packet ，如果需要） .
回复于 2024-04-28T06:40:07+08:00

MySQL DROP所有表，忽略外键

21 回答

相关问题