是否有一个很好的简单的方法来删除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更改为数据库的名称,当然还有user/pass。

其他回答

基于@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}

在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更改为数据库的名称,当然还有user/pass。

这是一个基于游标的解决方案。有点长,但可以作为单个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语句(我喜欢增加的安全性)。

从这个答案来看,

执行:

  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