MySQL - 如何在单个数据库中清空所有表中的数据

Question

我有一个名为Database1的数据库，其中包含40个表格。现在我想要删除这40个表格中的所有数据。我知道可以使用以下方法来显示该数据库中的所有表格：

SELECT table_name 
FROM INFORMATION_SCHEMA.tables 
WHERE table_schema = 'Database1';

如何使用单个查询从Database1中删除所有表的数据？

注意：

我只想删除数据，而不是表。

我正在使用mysql workbench 6.0

- MMMMS

---

删除 Database1; 创建 Database1 并且为空。 - Bernd Buffen

那也会丢失表格定义... - eggyal

SELECT GROUP_CONCAT(CONCAT('TRUNCATE TABLE ',t.TABLE_SCHEMA,'.',t.TABLE_NAME,';\n') SEPARATOR '') FROM INFORMATION_SCHEMA.tables t WHERE t.table_schema = 'Database1' - 它也会删除视图。查询结果。 - Bernd Buffen

Answer 1

您可以尝试这样做：

mysqldump -d -uuser -ppass --add-drop-table yourdatabasename > yourdatabasename.sql
mysql -uuser -ppass yourdatabasename < yourdatabasename.sql

正如Zafar正确指出的那样，如果您想要包含存储过程/函数，则可以使用-R选项进行包含。

或者您可以尝试像这样：

mysql -Nse 'show tables' yourdatabasename | while read table; do mysql -e "truncate table $yourtable" yourdatabasename; done

- Rahul Tripathi

---

或者作为一个命令，只需将mysqldump输出导入到mysql中，省去中间文件... - eggyal

关于您使用“截断”的编辑，请参阅我在@Zafer答案中提到的要点：“小心——$table应该真正地被引用和转义（它可能包含在未引用标识符中无效的字符）”。我认为使用mysqldump的答案更安全。 - eggyal

@eggyal：是的，同意，我也更喜欢使用mysqldump。但只是添加了一个选项。 :) - Rahul Tripathi

@RahulTripathi：这个转储技术会丢失存储过程/函数，所以也要包括-R。 - Zafar Malik

@RahulTripathi，请注意你的方法也会保留AUTO_INCREMENT计数器，不像“TRUNCATE TABLE”。这是好事还是坏事，应该由OP自行考虑。 - vhu

@ZafarMalik：为什么会丢失存储过程？它们没有被删除任何地方... - eggyal

Answer 2

您可以在服务器控制台上执行以下命令。

mysql -uroot -p<pass> -Nse 'show tables' database1 | while read table; do mysql -uroot -p<pass> database1 -e "truncate table $table"; done

你也可以通过任何图形界面工具（如sqlyog）按以下步骤执行：

right click on database1 > choose more database options > truncate database

第三个选项是按照以下结构备份和恢复-

mysqldump -R -d -uroot -proot123 database1 | mysql -uroot -proot123 database1

注意：如果您使用存储过程/函数，始终使用-R，否则您将失去它。

享受...

- Zafar Malik

---

小心——$table 应该被引用和转义（它可能包含在未引用标识符中无效的字符）。我发现 @Rahul 的答案使用 mysqldump 更安全。 - eggyal

@eggyal：如果没有使用-R参数，mysqldump在备份文件中将不会包含存储过程/函数。 - Zafar Malik

这是正确的，但存储过程/函数从原始数据库中永远不会被删除 - 只有表会被删除（如果您在mysqldump中指定了--add-drop-table选项，而您没有这样做，因此实际上您的版本将由于表已经存在而失败）。 - eggyal

根据我的了解，--add-drop-table 是 mysqldump 的默认选项，因此无需包含它...另外，如果备份文件不包含存储过程/函数模式，则它们将如何被还原。 - Zafar Malik

数据库永远不会被删除。存储过程也永远不会被删除。因此，没有必要“恢复”它们。 - eggyal

@eggyal：是的，你说得对，在这里表将被删除并重新创建，而不是数据库/存储过程。 - Zafar Malik

Answer 3

针对Oracle数据库： 如果您想删除所有表中的所有记录而不删除表，请查看以下链接，它可能会帮助您 https://dba.stackexchange.com/questions/74519/delete-all-the-data-from-all-tables

您可以尝试以下任何一种方法： 从MYSQL中删除所有表中的数据

如何在mysql中清空所有表中的所有行（使用sql）

- Vishal Barot

---

该链接的问题（及其答案）仅适用于Oracle。 - eggyal