MySQL DROP все таблицы, игнорируя внешние ключи

Есть ли хороший простой способ удалить все таблицы из базы данных MySQL, игнорируя любые ограничения внешнего ключа, которые могут быть там?

Ответы

Ответ 1

Я нашел полезный набор сгенерированных наборов высказываний и рекомендую следующие настройки:

  • Ограничьте сгенерированные капли в своей базе данных следующим образом:
SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';
  1. Примечание, за http://dev.mysql.com/doc/refman/5.5/en/drop-table.html падение с помощью каскада бессмысленно/вводит в заблуждение:

"RESTRICT и CASCADE разрешено упростить перенос. В MySQL 5.5 они ничего не делают".

Поэтому, чтобы операторы drop работали, если вам нужно:

SET FOREIGN_KEY_CHECKS = 0

Это приведет к отключению проверки ссылочной целостности - поэтому, когда вы закончите выполнение необходимых капель, вам понадобится reset проверка ключа с помощью

SET FOREIGN_KEY_CHECKS = 1

Ответ 2

Из 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;

(Обратите внимание, что это дает ответ на то, как отключить проверки внешнего ключа, чтобы иметь возможность отбрасывать таблицы в произвольном порядке. Он не отвечает, как автоматически создавать инструкции выпадающих таблиц для всех существующих таблиц и выполнять их в одном script. ответ Jean делает.)

Ответ 3

Здесь хранится хранимая процедура 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

От этого ответа,

выполнить:

  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 принятый ответ проще, за исключением того, что вам нужно выполнить его дважды, сначала для получения запроса, а второй для выполнения запроса. Я предоставил несколько глупых back-ticks, чтобы избежать специальных символов в именах db и таблиц.

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

Ответ 5

Здесь находится решение на основе курсора. Kinda longy, но работает как единый пакет 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`;

Ответ 6

Вы можете сделать:

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

Затем запустите сгенерированные запросы. Они будут удалять каждую таблицу в текущей базе данных.

Здесь есть помощь по команде drop table.

Ответ 7

Я придумал эту модификацию для ответа 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';

Это возвращает всю вещь в одном поле, поэтому вы можете скопировать один раз и удалить все таблицы (используйте Copy Field Content (unquoted) в Workbench). Если у вас много таблиц, вы можете нанести некоторые ограничения на GROUP_CONCAT(). Если это так, увеличьте максимальную переменную len (и max_allowed_packet, если необходимо).

Ответ 8

Если в linux (или любой другой системе, поддерживающей трубопроводы, эхо и 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;

Я знаю, что это старый вопрос, но я думаю, что этот метод быстрый и простой.

Ответ 9

каждый вышеприведенный подход включает намного больше работы, чем этот 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

Ответ 10

Вот автоматический способ сделать это с помощью bash script:

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

Ответ 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');

Не забудьте изменить YOURDB на имя вашей базы данных и, очевидно, пользователь/пароль.

Ответ 12

В оболочке Linux, например bash/zsh:

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 включить ваши кредиты.)

Ответ 13

Это довольно старый пост, но ни один из ответов здесь действительно не ответил на мой вопрос, поэтому я надеюсь, что мой пост поможет людям!

Я нашел это решение по другому вопросу, который очень хорошо работает для меня:

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.

Надеюсь, это поможет!

Ответ 14

Один вкладыш для удаления всех таблиц из данной базы данных:

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

Выполнение этого приведет к удалению всех таблиц из базы данных DATABASE_NAME.

И хорошо, что имя базы данных написано явно только один раз.

Ответ 15

Основываясь на ответе @Dion Truter и @Wade Williams, следующая оболочка script будет удалять все таблицы, сначала показывая, что она собирается запускать, и дает вам возможность прервать использование 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} [email protected] >> ${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} [email protected] < ${TMPFILE}
echo "Exit status is ${?}."
rm ${TMPFILE}

Ответ 16

Я использую следующее с сервером 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 (мне нравится добавленная безопасность).

Ответ 17

Лучшее решение для меня до сих пор

Выберите База данных → Щелкните правой кнопкой мыши → Задачи → Сгенерировать скрипты - откроет мастер для генерации скриптов. После выбора объектов в параметре Set Scripting нажмите Расширенная кнопка. В разделе "Script DROP и CREATE" выберите Script DROP.

Запустите script.