MySQL 如何清空mysql中所有表中的所有行（在sql中）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/454174/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to empty all rows from all tables in mysql (in sql)
提问by Dana the Sane
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)?
我正在编写一些 db 实用程序脚本，我需要做的任务之一是仅重建数据，但保持架构完好无损。使用 bash 和 mysql 工具（无 php 等）从命令行自动执行此操作的最简单方法是什么？
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.
更新：我希望解决方案可以在一个命令中处理所有表，如果可能，如果添加或删除表，则不需要更新。
回答by UnkwnTech
TRUNCATE tableName;
This will empty the contents of the table.
这将清空表的内容。
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.
针对 Q 编辑进行编辑：从我的快速测试看来，您必须至少执行 2 次查询，因为“显示表”似乎不能用作子查询，我不知道如何在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]);
}
?>
At a minimum this needs some error handling.
至少这需要一些错误处理。
回答by Brian Fisher
If you are on unix/linux you can use the shell to run:
如果您使用的是 unix/linux，则可以使用 shell 运行：
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
Or Rational Relational has a blogpost on how to write stored procedure to do this.
回答by rjamestaylor
Here's a BASH one-liner to truncate all tables from a list of databases:
这是从数据库列表中截断所有表的 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
Please note, truncating will remove all the data from the target tables without any prompting. Perhaps change "truncate $i" to "describe $i" first to make sure the tables in the result set are the ones intended to be emptied.
请注意，截断将在没有任何提示的情况下从目标表中删除所有数据。也许首先将“truncate $i”更改为“describe $i”，以确保结果集中的表是要清空的表。
One more thing: if you want to iterate over every table in all MySQL databases (except information_schemaand mysql, I would hope!), substitute the following for the above "database_name1 database_name2":
还有一件事：如果您想遍历所有 MySQL 数据库中的每个表（除了information_schemaand mysql，我希望！），请将以下内容替换为上面的“database_name1 database_name2”：
`echo 'show databases' | mysql | awk ' != "information_schema" && \
!= "mysql" {if (NR > 1) {print}}'`
So, here's a sample that's less destructive; it performs OPTIMIZE for all tables in every MySQL database (exceptions as noted above):
所以，这是一个破坏性较小的样本；它对每个 MySQL 数据库中的所有表执行 OPTIMIZE（上述例外情况）：
for j in `echo 'show databases' | mysql | \
awk ' != "information_schema" && != \
"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
Modify the "action" performed as needed and with much trepidation!
根据需要修改执行的“动作”，并且非常担心！
回答by mathijsuitmegen
For people who want to do this via phpMyAdmin, have a look at this question:
对于想要通过 phpMyAdmin 执行此操作的人，请查看以下问题：
