代码之家  ›  专栏  ›  技术社区  ›  bcmcfc

mysql删除所有表,忽略外键

  •  326
  • bcmcfc  · 技术社区  · 14 年前

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

    22 回复  |  直到 6 年前
        1
  •  370
  •   izogfif Bhond    7 年前

    我发现生成的drop语句集很有用,并建议进行以下调整:

    1. 将生成的数据滴限制到数据库,如下所示:
    SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
    FROM information_schema.tables
    WHERE table_schema = 'MyDatabaseName';
    

    注意:这不会执行DROP语句,它只是给您一个它们的列表。 您将需要剪切输出并将其粘贴到SQL引擎中以执行它们。

    1. 注: http://dev.mysql.com/doc/refman/5.5/en/drop-table.html ,使用cascade丢弃是无意义的/误导的:

    “限制和级联允许使移植更容易。在MySQL5.5中,它们什么也不做。”

    因此,如果需要,为了使DROP语句工作:

    SET FOREIGN_KEY_CHECKS = 0
    

    这将禁用参照完整性检查-因此,当您完成所需的删除操作后,您将希望使用重置键检查

    SET FOREIGN_KEY_CHECKS = 1
    
    1. 最终执行应该如下:
    SET FOREIGN_KEY_CHECKS = 0;
    -- Your semicolon separated list of DROP statements here
    SET FOREIGN_KEY_CHECKS = 1;
    

    注意:为了更容易使用select的输出,mysql-b选项可以帮助您。

        2
  •  122
  •   chiccodoro    10 年前

    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语句,并在单个脚本中执行这些语句。 姬恩的回答 是吗?)

        3
  •  83
  •   Alexey Morozov    12 年前

    以下是修改了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`;
    
        4
  •  27
  •   SkyLeach    9 年前

    上面的每一个方法都比这一个方法包含了更多的工作…

    ( 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
    
        5
  •  18
  •   Community CDub    8 年前

    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接受的答案更简单,除非您需要执行两次,第一次是获取查询,第二次是执行查询。我提供了一些愚蠢的反勾号来避开db和表名中的特殊字符。

      SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
      FROM   information_schema.tables
      WHERE  table_schema = 'dbName'; --your db name here
    
        6
  •  16
  •   SurlyDre    13 年前

    这是一个基于光标的解决方案。有点冗长,但作为单个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`;
    
        7
  •  11
  •   Pablo Santa Cruz    14 年前

    你可以做到:

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

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

    Here 有一些帮助 drop table 命令。

        8
  •  8
  •   kitti    11 年前

    我对迪翁·特鲁特的答案进行了修改,使许多表格更容易使用:

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

    这将在一个字段中返回整个内容,因此您可以复制一次并删除所有表(使用 Copy Field Content (unquoted) 在工作台)。如果你有很多桌子,你可能会碰到一些限制 GROUP_CONCAT() . 如果是这样,增加max len变量(和 max_allowed_packet ,如有必要)。

        9
  •  5
  •   kfox    12 年前

    下面是通过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
    
        10
  •  5
  •   moretti.fabio    10 年前

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

    我知道这是一个古老的问题,但我认为这个方法快速简单。

        11
  •  5
  •   mgershen    7 年前

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

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

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

    这方面的一个好处是数据库名称只显式地写一次。

        12
  •  2
  •   nsbucky    11 年前

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

    只需记住将RDB更改为数据库的名称,显然是用户/密码。

        13
  •  2
  •   artfulrobot    10 年前

    在类似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)来实际 手术室想要的工作。

    注释 当然,除非您的安全设置非常低,否则您也必须在这些mysql命令中输入(两次)您的凭证。(或者您可以将mysql命令别名为包含您的凭据。)

        14
  •  2
  •   Sergey Alaev    7 年前

    googling on topic总是让我想到这个问题,所以这里使用的是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`;
    
        15
  •  2
  •   Pavel.Solovyenko    6 年前

    在这里放一些有用的 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
    

    它对我有帮助,我希望它能有用

        16
  •  1
  •   mokk    9 年前

    这是一个相当古老的帖子,但在我看来,这里的答案都没有真正回答这个问题,所以我希望我的帖子能帮助人们!

    我在另一个问题上找到了这个解决方案,这个问题对我来说非常有效:

    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 命令行。

    希望这有帮助!

        17
  •  1
  •   Philip Callender    8 年前

    在@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}
    
        18
  •  0
  •   pgmank    7 年前

    此解决方案基于@skyligh answer,但支持使用外键删除表。

    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
    
        19
  •  0
  •   Ernestas Stankevičius    6 年前
    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"
    
        20
  •  0
  •   Sanjay    6 年前

    Simple and Clear(may be).

    可能不是一个很好的解决方案,但这对我很有帮助,挽救了我的一天。

    Worked for Server version:5.6.38 mysql community server(gpl)

    我遵循的步骤:

    <预> <代码> 1。使用concat和group_concat生成drop查询。 2。使用数据库 三。禁用键约束检查 4。复制从步骤1生成的查询 5。启用键约束检查 6。运行显示表 < /代码>

    MySQL外壳

    mysql>系统清除;
    mysql>选择concat('drop table if exists`',group_concat(table_name separator'`,`'),'`;')作为信息_schema.tables的dropquery,其中table_schema='emall_duplicate';
    +————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————--+
    |删除查询|
    +————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————--+
    |删除表if exists`admin``app``app-meta-settings``commission``commission-history``coupon``email-templates``infopages``invoice``m-pc-xref``member``commhant``message-templates``m notification``mshipping-address``notification``order``orderdetail``patAttributes``pbrand``pcategory``permissions``pfeatures`,` pimage`,`preport`,`product`,`product_review`,`psspecification`,`ptechnical_specification`,`pwishlist`,`role_perms`,`roles`,`settings`,`test`,`testanother`,`user_perms`,`user_roles`,`users`,`wishlist`;|
    +————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————--+
    一组1行(0.00秒)
    
    mysql>使用emall_duplicate;
    数据库已更改
    mysql>设置外键检查=0;查询确定,0行受影响(0.00秒)
    
    //复制并粘贴从步骤1生成的查询
    mysql>drop table if exists`admin`,`app`,`app-meta-settings`,`commission`,`commission-history`,`coupon`,`email-templates`,`infopages`,`invoice`,`m-pc-xref`,`member`,`commhant`,`message-templates`,`m notification`,`mshipping-address`,`notification`,`order`,`orderdetail`,`patAttributes`,`pbrand`,`pcategory`,`permissions`,`pfea特性`,`PImage`,`preport`,`product`,`product ` review`,`psspecification`,`ptechnical `规格`,`pwishlist`,`role ` perms`,`roles`,`settings`,`test`,`testanother`,`user ` perms`,`user ` roles`,`users`,`wishlist`;
    查询正常,0行受影响(0.18秒)
    
    mysql>设置外键检查=1;
    查询正常,0行受影响(0.00秒)
    
    mysql>显示表格;
    空设置(0.01秒)
    
    MySQL & GT;
    < /代码> 
    
    

    ER版本: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> 
    

    enter image description here

        21
  •  -1
  •   Christiaan Maks    10 年前

    我将以下内容用于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!!!!')
    

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

        22
  •  -6
  •   azza idz    9 年前

    最佳解决方案 对我来说,到目前为止

    选择 数据库->右键单击->任务->生成脚本 -将打开向导以生成脚本。在“设置脚本”选项中选择对象后,单击 高级按钮 . 下 “编写拖放和创建脚本” 选择 脚本删除 .

    运行脚本。