这里，我知道这里

   SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
    FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('databasename1','databasename2');

如果不能删除或更新父行:外键约束失败

如果存在外键指向您试图删除/截断的表的表，就会发生这种情况。

在截断表之前，你需要做的是:

SET FOREIGN_KEY_CHECKS=0;

截断您的表并将其更改回

SET FOREIGN_KEY_CHECKS=1; 

使用这段PHP代码 $truncate = mysql_query("SELECT Concat(' truncate TABLE '，table_schema，'. "'，TABLE_NAME， ';')作为table_query FROM INFORMATION_SCHEMA。table where table_schema in ('databasename')"); 而($ truncateRow =作用(截断)美元){ mysql_query()美元truncateRow [' tables_query ']); ｝ ? > 查看详情 链接

下面是一个截断本地数据库中所有表的过程。

如果它不起作用，请告诉我，我会删除这个答案。

未测试的

CREATE PROCEDURE truncate_all_tables()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE cmd VARCHAR(2000);

   -- Declare the cursor
   DECLARE cmds CURSOR
   FOR
   SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES;

   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Open the cursor
   OPEN cmds;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH cmds INTO cmd;

      -- Execute the command
      PREPARE stmt FROM cmd;
      EXECUTE stmt;
      DROP PREPARE stmt;

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE cmds;

END;

我不确定，但我认为有一个命令可以使用，你可以复制数据库的模式到新的数据库，一旦你这样做了，你可以删除旧的数据库，这之后，你可以再次复制数据库模式到旧的名称。

To truncate a table, one must drop the foreign key constraints mapped to the columns in this table from other tables (in fact on all tables in the specific DB/Schema). So, all foreign key constraints must be dropped initially followed by table truncation. Optionally, use the optimize table (in mysql, innodb engine esp) to reclaim the used data space/size to OS after data truncation. Once data truncation is carried out, create the same foreign key constraints again on the same table. See below a script that would generate the script to carry out the above operations. SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';') FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='<TABLE SCHEMA>' UNION SELECT CONCAT('TRUNCATE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE' UNION SELECT CONCAT('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE' UNION SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ADD CONSTRAINT ',CONSTRAINT_NAME,' FOREIGN KEY(',COLUMN_NAME,')',' REFERENCES ',REFERENCED_TABLE_NAME,'(',REFERENCED_COLUMN_NAME,');') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME LIKE 'FK%' AND TABLE_SCHEMA='<TABLE SCHEMA>' INTO OUTFILE "C:/DB Truncate.sql" LINES TERMINATED BY '\n';

现在，运行Db Truncate。生成SQL脚本

的好处。 1)回收磁盘空间 2)不需要删除并重新创建具有相同结构的DB/Schema

缺点。 1) FK约束应该是表中的名称，约束名称中包含“FK”。

删除(即删除表格)

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done

截断(即空表)

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done

这对我很管用。修改数据库、用户名和密码。

mysql -Nse 'show tables' -D DATABASE -uUSER -pPWD | while read table; do echo "SET FOREIGN_KEY_CHECKS = 0;drop table \`$table\`;SET FOREIGN_KEY_CHECKS = 1;"; done | mysql DATABASE -uUSER -pPWD