如何在 MySQL（使用 SQL）中清空所有表中的所有行

Question

我正在编写一些数据库实用程序脚本，其中一个任务是仅重建数据，但保留模式。使用bash和mysql工具（无需php等），从命令行自动完成这个最简单的方法是什么？

更新： 我希望解决方案能够一次处理所有表格，并且如果可能的话，不需要在表格添加或删除时进行更新。

- Dana the Sane

Answer 1

TRUNCATE tableName;

这将清空表格的内容。

根据编辑的回答，从我的快速测试中看来，你至少需要进行两个查询，因为似乎“show tables”不能用作子查询。我不知道如何在bash中执行此操作，所以这里提供了一个PHP示例，希望能有所帮助。

<?php      
mysql_connect('localhost', 'user', 'password');
$dbName = "database";
mysql_select_db($dbName); /*added semi-colon*/
$result_t = mysql_query("SHOW TABLES");
while($row = mysql_fetch_assoc($result_t))
{
   mysql_query("TRUNCATE " . $row['Tables_in_' . $dbName]);
}
?>

至少需要一些错误处理。

- Unkwntech

---

有没有办法可以选择模式中所有表的名称并将其提供给此脚本？否则，当添加新表时，我就必须更新脚本。 - Dana the Sane

我替你试过 TRUNCATE *，但没有成功。看起来你需要运行一个 SHOW TABLES 查询，并在此基础上进行操作。 - Ross

我找到了这个链接http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-i.aspx，其中有一个存储过程可以很容易地适应你的需求，或者直接使用也可以。 - UnkwnTech

Answer 2

如果您使用的是Unix/Linux系统，您可以使用shell运行以下命令：

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

或者说Rational Relational在其博客文章中介绍了如何编写存储过程来实现此功能。

- Brian Fisher

---

这正是我在寻找的解决方案，但不幸的是它在外键约束上失败了。由于我不想放弃所有的约束并重新添加它们，编写自己的截断方法最终效果更好。 - Dana the Sane

你可以尝试使用 SET FOREIGN_KEY_CHECKS = 0; - Brian Fisher

这是一个不错的方法，但为什么要使用grep DROP？OP要求保留模式的完整性，而这种方式只会删除表。跳过grep部分，脚本就可以完美运行。 - Andreas Wederbrand

Answer 3

这是一个BASH单行命令，用于截断数据库列表中所有表格：

for j in database_name1 database_name2; \
do for i in `echo 'show tables ' |mysql $j \
|grep -v 'Tables_in'`; do mysql $j -e "truncate $i"; done; done

请注意，截断将在没有任何提示的情况下从目标表中删除所有数据。也许先将“truncate $i”更改为“describe $i”，以确保结果集中的表是要清空的那些表。

还有一件事：如果您想迭代所有MySQL数据库中的每个表（除了information_schema和mysql，我希望不包括这两个！），请用以下内容替换上面的“database_name1 database_name2”：

`echo 'show databases' | mysql | awk '$1 != "information_schema" && \
$1 != "mysql" {if (NR > 1) {print}}'`

所以，这里有一个不那么具有破坏性的示例；它对每个MySQL数据库中的所有表执行OPTIMIZE（如上所述的例外除外）：

for j in `echo 'show databases' | mysql | \
awk '$1 != "information_schema" && $1 != \
"mysql" {if (NR > 1) {print}}'`; do for i in \
`echo 'show tables ' |mysql $j |grep -v \
'Tables_in'`; do mysql -e "optimize table $j.$i"; \
done; done

根据需要和谨慎修改执行的“操作”！

- rjamestaylor

---

这在外键引用中无法工作。您会收到外键约束错误。 - Ray Booysen

现在我认为你会发现大多数MySQL用户使用FK，我肯定会使用。 - Dónal

Don：虽然我同意使用FK约束是正确和推荐的，但我怀疑“大多数MySQL用户”是否使用它们。在过去的一年中，我见过一千多个MySQL实例，很少有人使用现代MySQL的完整关系和ACID功能。话虽如此，转储/重新加载模式是最好的方法。 - rjamestaylor

这实际上是一个非常聪明的想法 - 为了绕过FK约束，只需将整个for循环的输出以单个命令管道传输到mysql中，如下所示：for i in $(echo 'SHOW TABLES' | mysql -uroot -p<pwd> <dbname> | grep -v "Tables_in"); do echo "DELETE FROM $i;"; done | mysql -uroot -p<pwd> <dbname> - John Calcote

Answer 4

对于想要通过phpMyAdmin完成此操作的人，请查看以下问题：

如何在不删除数据库的情况下，在phpMyAdmin中删除所有表格的内容？

- mathijsuitmegen