从MYSQL中的所有表中删除数据 [英] Delete data from all tables in MYSQL

问题描述

我有100个表，每个表40,000行.我想进入MySQL并从 all 表中删除 all 行.

I have 100 tables, 40,000 rows in each table. I want to go into MySQL and delete all rows from all tables.

...如果可能，用1条语句?

...in 1 statement, if possible?

我要保留数据库和表.

推荐答案

我不这么认为(但以前我做错了).我倾向于做的是两个步骤.

I don't think so (but I've been wrong before). What I tend to do is those cases is a two-step process.

如果您的DBMS具有命令行界面，则可以使用它来创建脚本来完成大部分工作，例如:

If your DBMS has a command line interface, you can use it to create a script to do the bulk of the work, something like:

db2 "select 'db2 delete from ' | tblname from sysibm.systables
    where owner = 'pax'" >p2.sh
p2.sh

第一位只是为pax拥有的每个表创建一个p2.sh文件(或Windows下的p2.cmd文件)，其中包含一个delete from语句.然后，您只需运行该命令文件即可完成工作.当然，您可能要先检查一下:-)

The first bit simply creates a p2.sh file (or a p2.cmd file under Windows) containing a delete from statement for every table owned by pax. Then you just run that command file to do the dirty work. You may want to check it first, of course :-)

这不是您正在寻找的一步式过程，但仍然非常简单.我在这里假设mysql也有一个命令行界面.

Not the one-step process you were looking for but still very simple. I'm assuming here that mysql also has a command line interface.

更新:

上面的MySQL版本看起来应该是这样的:

The MySQL version of the above looks like it should be:

echo "select 'mysql truncate table ' | table_name
              from information_schema.tables" | mysql >p2.sh
bash p2.sh

这使用truncate命令来删除所有行，通常比delete from更有效.它还使用适当的MySQL系统表来获取表名.

This uses the truncate command which is usually more efficient than delete from for deleting all rows. It also uses the proper MySQL system tables to get the table names.

尽管有一点-您可能希望在该选择项上放置where子句，以将表限制为要删除的表.按原样查询将尝试删除每个表.一种可能性是将其限制为特定的table_schema和/或table_type值.

One point though - you may want to put a where clause on that select to limit the tables to those you want deleted. The query as it stands will try to delete every table. One possibility is to limit it with specific table_schema and/or table_type values.

这篇关于从MYSQL中的所有表中删除数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！