是否有查询（命令）在一次操作中截断数据库中的所有表？我想知道我是否可以使用一个查询来执行此操作 .

24 回答

我发现只需执行下面的代码，只需用自己的代码替换表名即可 . 重要的是确保最后一行总是SET FOREIGN_KEY_CHECKS = 1;

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `table1`;
TRUNCATE `table2`;
TRUNCATE `table3`;
TRUNCATE `table4`;
TRUNCATE `table5`;
TRUNCATE `table6`;
TRUNCATE `table7`;
SET FOREIGN_KEY_CHECKS=1;

回复于 2024-04-29T05:25:47+08:00

掉落（即删除表格）

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

回复于 2024-04-29T05:25:47+08:00

1
截断Mysql实例上的多个数据库表
```
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('db1_name','db2_name');
```
使用“查询结果”截断表

注意：您可能会收到此错误：
```
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
```
如果存在对您尝试删除/截断的表的外键引用的表，则会发生这种情况 .

截断表之前您需要做的就是：
```
SET FOREIGN_KEY_CHECKS=0;
```
截断表格并将其更改回
```
SET FOREIGN_KEY_CHECKS=1;
```
回复于 2024-04-29T05:25:47+08:00
5

以这种方式使用phpMyAdmin：

数据库视图=>全部检查（表格）=>清空

如果要忽略外键检查，可以取消选中以下框：

[]启用外键检查

您需要运行至少4.5.0或更高版本才能获得此复选框 .

它不是MySQL CLI-fu，但是嘿，它有效！

回复于 2024-04-29T05:25:47+08:00
3
MS SQL Server 2005（删除PRINT以实际执行...）
```
EXEC sp_MSforeachtable 'PRINT ''TRUNCATE TABLE ?'''
```
如果数据库平台支持INFORMATION_SCHEMA视图，请获取以下查询的结果并执行它们 .
```
SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
```
试试MySQL吧：
```
SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
```
在Concat中添加分号可以更容易地使用分号 . 来自mysql workbench .
```
SELECT Concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
```
回复于 2024-04-29T05:25:47+08:00
122
我发现这会删除数据库中的所有表：
```
mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | mysql -uUSERNAME -pPASSWORD DATABASENAME
```
如果您受托管解决方案的限制（无法删除整个数据库），则非常有用 .

我修改它以截断表 . mysqldump没有“--add-truncate-table”，所以我做了：
```
mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | sed -e 's/DROP TABLE IF EXISTS/TRUNCATE TABLE/g' | mysql -uUSERNAME -pPASSWORD DATABASENAME
```
适合我 - 编辑，修复最后一个命令中的拼写错误
回复于 2024-04-29T05:25:47+08:00

这将打印命令以截断所有表：

SELECT GROUP_CONCAT(Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME) SEPARATOR ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('my_db');

回复于 2024-04-29T05:25:47+08:00

SET FOREIGN_KEY_CHECKS = 0;

SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ';')
FROM   information_schema.tables
WHERE  table_type   = 'BASE TABLE'
  AND  table_schema IN ('db1_name','db2_name');

PREPARE stmt FROM @str;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

回复于 2024-04-29T05:25:47+08:00

要截断表，必须从其他表中删除映射到此表中列的外键约束（实际上是在特定DB / Schema中的所有表上） .
因此，必须首先删除所有外键约束，然后删除表截断 .
（可选）使用优化表（在mysql，innodb engine esp中）在数据截断后将已使用的数据空间/大小回收到OS .
执行数据截断后，在同一个表上再次创建相同的外键约束 . 请参阅下面一个脚本，该脚本将生成执行上述操作的脚本 .

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” .

回复于 2024-04-29T05:25:47+08:00

2
如果使用sql server 2005，则有一个隐藏的存储过程，允许您对数据库中的所有表执行命令或一组命令 . 以下是使用此存储过程调用 TRUNCATE TABLE 的方法：
```
EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?"
```
Here是一篇很好的文章，进一步阐述 .

但是，对于MySql，您可以使用mysqldump并指定 --add-drop-tables 和 --no-data 选项以删除并创建忽略数据的所有表 . 像这样：
```
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE]
```
mysqldump usage guide from dev.mysql
回复于 2024-04-29T05:25:47+08:00

使用它并形成查询

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in (db1,db2)
INTO OUTFILE '/path/to/file.sql';

现在使用它来使用此查询

mysql -u username -p </path/to/file.sql

如果你得到这样的错误

ERROR 1701 (42000) at line 3: Cannot truncate a table referenced in a foreign key constraint

最简单的方法是在文件顶部添加此行

SET FOREIGN_KEY_CHECKS=0;

这表示我们不希望在浏览此文件时检查外键约束 .

它将截断数据库db1和bd2中的所有表 .

回复于 2024-04-29T05:25:47+08:00

-6

没有 . 没有一个命令可以一次截断所有mysql表 . 您必须创建一个小脚本来逐个截断表 .

ref：http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

回复于 2024-04-29T05:25:47+08:00

我知道这里

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, ';') as tables_query FROM INFORMATION_SCHEMA.TABLES where table_schema in ('databasename')");

    while($truncateRow=mysql_fetch_assoc($truncate)){

        mysql_query($truncateRow['tables_query']);

    }
?>

查看详细信息link

回复于 2024-04-29T05:25:47+08:00

-1

这是一个应该截断本地数据库中所有表的过程 .

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

Untested

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;

回复于 2024-04-29T05:25:47+08:00

23
我发现TRUNCATE TABLE ..有外键约束的问题，即使在NOCHECK CONSTRAINT ALL之后也是如此，所以我使用了DELETE FROM语句 . 这意味着不会重置身份种子，您可以随时添加DBCC CHECKIDENT来实现此目的 .

我使用下面的代码打印到消息窗口sql用于截断数据库中的所有表，然后再运行它 . 它只是让错误变得有点困难 .
```
EXEC sp_MSforeachtable 'PRINT ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'''
EXEC sp_MSforeachtable 'print ''DELETE FROM ?'''
EXEC sp_MSforeachtable 'print ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'''
```
回复于 2024-04-29T05:25:47+08:00

这是我的变体'有一个陈述截断'他们所有' .

首先，我使用一个名为“util”的独立数据库作为我的帮助程序存储过程 . 截断所有表的存储过程的代码是：

DROP PROCEDURE IF EXISTS trunctables;
DELIMITER ;;
CREATE  PROCEDURE trunctables(theDb varchar(64))
BEGIN
    declare tname varchar(64);
    declare tcursor CURSOR FOR 
    SELECT table_name FROM information_schema.tables WHERE table_type <> 'VIEW' AND table_schema = theDb;
    SET FOREIGN_KEY_CHECKS = 0; 
    OPEN tcursor;
    l1: LOOP
        FETCH tcursor INTO tname;
        if tname = NULL then leave l1; end if;
        set @sql = CONCAT('truncate `', theDB, '`.`', tname, '`');
        PREPARE stmt from @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP l1;
    CLOSE tcursor;
    SET FOREIGN_KEY_CHECKS = 1; 
END ;;
DELIMITER ;

一旦在util数据库中有了这个存储过程，就可以调用它

call util.trunctables('nameofdatabase');

这现在正是一个声明:-)

回复于 2024-04-29T05:25:47+08:00

0

我不确定，但我认为有一个命令可以将数据库模式复制到新数据库中，一旦完成此操作，就可以删除旧数据库，然后再次将数据库模式复制到旧名称 .

回复于 2024-04-29T05:25:47+08:00
0
我知道这不是一个命令，但可以通过以下步骤从phpMyAdmin中实现所需的结果：
- 选择（所有）要删除的表（全部检查）
- 从"With selected:"列表中选择"Drop" / "Truncate"
- 在确认页面（"Do you really want to:"）复制查询（红色背景的所有内容）
- 转到顶部并单击SQL并写入："SET FOREIGN_KEY_CHECKS=0;"然后粘贴以前复制的查询
- 点击"Go"
我们的想法是快速从数据库中获取所有表（您在5秒内完成，然后单击2次），但首先禁用外键检查 . 没有CLI，也没有丢弃数据库并再次添加它 .
回复于 2024-04-29T05:25:47+08:00

TB=$( mysql -Bse "show tables from DATABASE" );
for i in ${TB};
    do echo "Truncating table ${i}";
    mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table DATABASE.${i}; set foreign_key_checks=1; set unique_checks=1";
    sleep 1;
done

大卫，

感谢您花时间格式化代码，但这是应该如何应用的 .

-Kurt

在UNIX或Linux机器上：

确保你在bash shell中 . 这些命令将从命令行运行，如下所示 .

注意：

我将我的凭据存储在〜/ .my.cnf文件中，因此我不需要在命令行上提供它们 .

注意：

cpm是数据库名称

我只是从每个命令中显示一小部分结果样本 .

找到您的外键约束：

klarsen@Chaos:~$ mysql -Bse "select concat(table_name, ' depends on ', referenced_table_name)
             from information_schema.referential_constraints
             where constraint_schema = 'cpm'
             order by referenced_table_name"

approval_external_system取决于approval_request
地址取决于客户
customer_identification取决于客户
external_id取决于客户
凭证取决于客户
email_address取决于客户
approval_request取决于客户
customer_status取决于客户
customer_image取决于客户

列出表和行计数：

klarsen@Chaos:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cpm'" | cat -n

 1  address 297
 2  approval_external_system    0
 3  approval_request    0
 4  country 189
 5  credential  468
 6  customer    6776
 7  customer_identification 5631
 8  customer_image  2
 9  customer_status 13639

截断你的表格：

klarsen@Chaos:~$ TB=$( mysql -Bse "show tables from cpm" ); for i in ${TB}; do echo "Truncating table ${i}"; mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table cpm.${i}; set foreign_key_checks=1; set unique_checks=1"; sleep 1; done

截断表格地址
截断表approval_external_system
截断表approval_request
截断表国家/地区
截断表凭证
截断表客户
截断表customer_identification
截断表customer_image
截断表customer_status

验证它是否有效：

klarsen@Chaos:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cpm'" | cat -n

 1  address 0
 2  approval_external_system    0
 3  approval_request    0
 4  country 0
 5  credential  0
 6  customer    0
 7  customer_identification 0
 8  customer_image  0
 9  customer_status 0
10  email_address   0

在Windows框中：

注意：

cpm是数据库名称

C:\>for /F "tokens=*" %a IN ('mysql -Bse "show tables" cpm') do mysql -e "set foreign_key_checks=0; set unique_checks=0; truncate table %a; foreign_key_checks=1; set unique_checks=1" cpm

回复于 2024-04-29T05:25:47+08:00

以下MySQL查询本身会生成一个查询，该查询将截断给定数据库中的所有表 . 它绕过FOREIGN键：

SELECT CONCAT(
         'SET FOREIGN_KEY_CHECKS=0; ',
         GROUP_CONCAT(dropTableSql SEPARATOR '; '), '; ',
         'SET FOREIGN_KEY_CHECKS=1;'
       ) as dropAllTablesSql
FROM   ( SELECT  Concat('TRUNCATE TABLE ', table_schema, '.', TABLE_NAME) AS dropTableSql
         FROM    INFORMATION_SCHEMA.TABLES
         WHERE   table_schema = 'DATABASE_NAME' ) as queries

回复于 2024-04-29T05:25:47+08:00

mysqldump -u root -p --no-data dbname > schema.sql
mysqldump -u root -p drop dbname
mysqldump -u root -p < schema.sql

回复于 2024-04-29T05:25:47+08:00

Soln 1）

mysql> select group_concat('truncate',' ',table_name,';') from information_schema.tables where table_schema="db_name" into outfile '/tmp/a.txt';
mysql> /tmp/a.txt;

索恩2）

- Export only structure of a db
- drop the database
- import the .sql of structure

编辑----

earlier in solution 1, i had mentioned concat() instead of group_concat() which would have not returned the desired result

回复于 2024-04-29T05:25:47+08:00

305
一个想法可能是删除并重新创建表格？

EDIT:

@Jonathan Leffler：是的

Other Suggestion （或者您不需要截断所有表格的情况）：

为什么不创建一个基本的存储过程来截断特定的表
```
CREATE PROCEDURE [dbo].[proc_TruncateTables]
AS
TRUNCATE TABLE Table1
TRUNCATE TABLE Table2
TRUNCATE TABLE Table3
GO
```
回复于 2024-04-29T05:25:47+08:00

<?php
// connect to database
$conn=mysqli_connect("localhost","user","password","database");

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// sql query
$sql =mysqli_query($conn,"TRUNCATE " . TABLE_NAME);


// Print message
if ($sql === TRUE) {
  echo 'data delete successfully';
}
else {
 echo 'Error: '. $conn->error;
}

$conn->close();

?>

这是我用来清除表格的代码片段 . 只需更改$ conn info和TABLE_NAME即可 .

回复于 2024-04-29T05:25:47+08:00

在一个命令中截断MySQL数据库中的所有表？

24 回答

掉落（即删除表格）

截断（即空表）

相关问题