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

From 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 для всех существующих таблиц и выполнять их в одном скрипте. Жан делает.)

вот хранимая процедура 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`;

С ответ,

выполнить:

  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.

---

или иначе, принятый ответ Диона проще, за исключением того, что вам нужно выполнить его дважды, во-первых, чтобы получить запрос, а во-вторых, чтобы выполнить запрос. Я предоставил некоторые глупые обратные тики, чтобы избежать специальных символов в именах БД и таблиц.

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

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

вот решение на основе курсора. Своего рода длинный, но работает как один пакет 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`;

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

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

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

здесь находится на

Я придумал эту модификацию на ответ Диона Трутера, чтобы сделать его проще со многими таблицами:

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 в случае необходимости).

вот автоматизированный способ сделать это с помощью скрипта bash:

host=
dbName=
user=
password=

if [ -z "" ]
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

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

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

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

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

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

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

в 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 на имя вашей базы данных, и, очевидно, пользователь/pass.

в оболочке 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) на самом деле do работа, которую хотел ОП.

Примечание конечно, вам придется ввести свои учетные данные (дважды) в этих командах mysql, тоже, если у вас нет очень низкой настройки безопасности. (или вы можете псевдоним вашей команды mysql, чтобы включить ваши creds.)

Googling по теме всегда приводит меня к этому вопросу 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`;

основываясь на ответе @Dion Truter и @Wade Williams, следующий сценарий оболочки удалит все таблицы, сначала показав, что он собирается запустить, и даст вам возможность прервать работу с помощью 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}

просто положите сюда некоторые полезные комментарий Джонатана Уотта удалить все таблицы

MYSQL="mysql -h HOST -u USERNAME -pPASSWORD DB_NAME"
$MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; drop table `"  "`;"}' | $MYSQL
unset MYSQL

Это помогает мне и я надеюсь, что это может быть полезным

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

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

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 командная строка.

надеюсь, что это помогает!

Это решение основано на ответе @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

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"

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

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

выберите База Данных - > Щелкните Правой Кнопкой Мыши - > Задачи - > Создать Скрипты - откроется мастер создания скриптов. После выбора объектов в параметре set Scripting нажмите Дополнительно. Под "Script DROP and CREATE" выберите падение скрипта.

запустить скрипт.