MySQL DROP все таблицы, игнорируя внешние ключи
Есть ли хороший простой способ удалить все таблицы из базы данных MySQL, игнорируя любые ограничения внешнего ключа, которые могут быть там?
28 ответов
Я нашел сгенерированный набор операторов drop полезным и рекомендую следующие настройки:
- Ограничьте сгенерированные капли в вашей базе данных следующим образом:
SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';
Примечание. Это не выполняет операторы DROP, а просто дает вам их список. Вам нужно будет вырезать и вставить вывод в ваш движок 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;
NB: чтобы легче использовать вывод SELECT, может помочь опция mysql -B.
С 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;
(Обратите внимание, что это ответ на вопрос о том, как отключить проверку внешнего ключа, чтобы иметь возможность отбрасывать таблицы в произвольном порядке. Он не отвечает, как автоматически генерировать операторы отбрасываемых таблиц для всех существующих таблиц и выполнять их в одном сценарии. Ответ Жана делает.)
Вот хранимая процедура 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`;
Каждый вышеприведенный подход включает в себя гораздо больше работы, чем этот 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
Из этого ответа
выполнить:
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
Вот решение на основе курсора. Вроде долго, но работает как один пакет 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`;
Один вкладыш для удаления всех таблиц из данной базы данных:
echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"
Выполнение этого удалит все таблицы из базы данных DATABASE_NAME.
И хорошо то, что имя базы данных пишется только один раз.
Одношаговое решение без копирования возвращаемого значения из запроса SQL Select с использованием процедуры.
SET FOREIGN_KEY_CHECKS = 0
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 FOREIGN_KEY_CHECKS = 1
Ты можешь сделать:
select concat('drop table if exists ', table_name, ' cascade;')
from information_schema.tables;
Затем запустите сгенерированные запросы. Они будут отбрасывать каждую таблицу в текущей базе данных.
Вот некоторая помощь по drop table команда.
Поиск по теме всегда приводит меня к такому вопросу, поэтому здесь работает код 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`;
Я придумал эту модификацию для ответа Дион Трутера, чтобы упростить многие таблицы:
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=$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
Если в 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;
Я знаю, что это старый вопрос, но я думаю, что этот метод быстрый и простой.
Удалите все таблицы из базы данных одной строкой из командной строки:
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]
Где [имя_пользователя], [пароль], [имя_хоста] и [имя_базы_данных] должны быть заменены реальными данными (пользователем, паролем, именем хоста, именем базы данных).
Просто мягкое напоминание,
Если это возможно и у вас нет других проблем, вы можете удалить базу данных и создать ее заново.
- удалить базу данных <database_name>
- создать базу данных <database_name>
Просто поместите здесь несколько полезных комментариев Джонатана Уотта, чтобы убрать все таблицы.
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
Это помогает мне, и я надеюсь, что это может быть полезно
В 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 на имя вашей базы данных и, очевидно, на пользователя / пароль.
В оболочке 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, чтобы включить ваши кредиты.)
Небольшое уточнение отличного ответа Науфала со следующими корректировками:
- нет ошибки, если в базе данных нет таблиц.
- сохраните и восстановите настройку FOREIGN_KEY_CHECKS.
- GROUP_CONCAT явный SEPARATOR для облегчения чтения кода.
- используй имя
dropTablesStmt, поскольку имя является глобальным и может перезаписать какой-либо другой уже подготовленный оператор с именемstmt.
use `database`;
SET @tables = NULL;
SET GROUP_CONCAT_MAX_LEN=1048576;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`' SEPARATOR ',') INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());
SELECT IF(@tables IS NULL,
'SELECT NULL FROM (SELECT NULL) AS `empty` WHERE 0=1',
CONCAT('DROP TABLE IF EXISTS ', @tables)) INTO @tables;
PREPARE dropTablesStmt FROM @tables;
SELECT @@FOREIGN_KEY_CHECKS INTO @SAVED_FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 0;
EXECUTE dropTablesStmt;
SET FOREIGN_KEY_CHECKS = @SAVED_FOREIGN_KEY_CHECKS;
DEALLOCATE PREPARE dropTablesStmt;
Просто и понятно (может быть).
Не может быть причудливым решением, но это сработало и спасло мой день.
Работал для версии сервера: 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>
Основываясь на ответе @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 -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 командная строка.
Надеюсь это поможет!
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"
если вы настроили PHPMyAdmin, вы можете удалить все таблицы с помощью PHPMyAdmin. пожалуйста, следуйте изображению ниже
Шаг 1: дважды щелкните имя базы данных
Шаг 2. На правом сайте откройте всю таблицу, затем выберите «Проверить все»
Шаг 4: выберите опцию раскрытия из раскрывающегося списка справа
Альтернативный шаг: нажмите кнопку сброса
Это решение основано на ответе @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
Все дали хорошие ответы, однако у меня есть альтернативный вариант для пользователей, знакомых с электронными таблицами/таблицами Excel. Согласно первому решению мы получаем список команд, но нам все еще нужно обрезать первый и последний символы ('|')
С "
show tables;"вы получите список всех таблиц;Теперь скопируйте результат и вставьте его в лист Excel (предположим, что все записи находятся в столбце «A» Excel)
Сначала вам нужно удалить первый и последний '|' символ-функция для удаления первого символа, т.е. '|'
=ВПРАВО(A1,ДЛИН(A1)-1)
функция для удаления последнего символа, т.е. '|' и добавьте конечную точку с запятой
=CONCAT(LEFT(B1,LEN(B1)-1),";")
Теперь создайте окончательный список запросов с помощью функции CONCAT.
=CONCAT("удалить таблицу",C1)
Я использую следующее с сервером 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 (мне нравится дополнительная безопасность).
Лучшее решение для меня до сих пор
Выберите База данных -> Правой кнопкой мыши -> Задачи -> Создать сценарии - откроется мастер для создания сценариев. После выбора объектов в настройке сценариев нажмите кнопку " Дополнительно". Под "Script DROP and CREATE" выберите Script DROP.
Запустить скрипт.