如何从mysql中的所有表中清空所有行(在SQL中) [英] How to empty all rows from all tables in mysql (in sql)

问题描述

我正在编写一些db实用程序脚本，我需要做的任务之一是仅重建数据，而保持架构不变.使用bash和mysql工具(无php等)从命令行自动执行此操作的最简单方法是什么?

I'm writing some db utility scripts, and one of the tasks I need to do is rebuild the data only, but leave the schema intact. What is the easiest way to automate this from the command-line using bash and the mysql tools (no php, etc)?

更新: 我希望该解决方案可以在一个命令中处理所有表，并且如果可能的话，如果添加或删除了表，则不需要更新.

Update: I'd like the solution to handle all tables in one command, and if possible, not need to be updated if tables are added or removed.

推荐答案

TRUNCATE tableName;

这将清空表的内容.

根据Q编辑进行 从我的快速测试看来，您将必须至少执行2个查询，因为似乎显示表"不能用作子查询，我不知道如何在bash中执行此操作，因此这是一个PHP示例，希望会有所帮助.

Edit in response to the Q edit: It seems from my quick test that you will have to do at least 2 queries as it seems that "show tables" cannot be used as a sub query, I don't know how to do this in bash so here is a PHP example, hopefully it will help.

<?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]);
}
?>

至少需要一些错误处理.

At a minimum this needs some error handling.

这篇关于如何从mysql中的所有表中清空所有行(在SQL中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！