是否有一个很好的简单的方法来删除MySQL数据库中的所有表，忽略任何外键约束，可能在那里?
当前回答
这是一个基于游标的解决方案。有点长，但可以作为单个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`;
其他回答
我使用以下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语句(我喜欢增加的安全性)。
你可以:
select concat('drop table if exists ', table_name, ' cascade;')
from information_schema.tables;
然后运行生成的查询。它们将删除当前数据库中的每一个表。
下面是删除表命令的一些帮助。
下面是一种通过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 -h$host -u$user -p$password $dbName -e "show tables" | grep -v Tables_in` ; do echo $i && mysql -h$host -u$user -p$password $dbName -e "SET FOREIGN_KEY_CHECKS = 0; drop table $i ; SET FOREIGN_KEY_CHECKS = 1" ; done
下面是修改后的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`;
这是一个基于游标的解决方案。有点长，但可以作为单个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`;
