MySQL 截断mysql数据库中的所有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3157671/
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
truncate all table in mysql database
提问by chetan
Is it possible to truncate all table in mysql database ? what is query for that .
是否可以截断 mysql 数据库中的所有表？什么是查询。
采纳答案by Pablo Santa Cruz
You can do this query:
您可以执行此查询：
select 'truncate table ' || table_name || ';'
from INFORMATION_SCHEMA.TABLES;
Then save results to a script and run it.
然后将结果保存到脚本并运行它。
Other possibility might be,
另一种可能是，
- Run the query
- Copy the results to the clipboard (COPY)
- Paste the results into the MySQL command interpreter (PASTE)
- 运行查询
- 将结果复制到剪贴板 (COPY)
- 将结果粘贴到 MySQL 命令解释器 (PASTE)
Done.
完毕。
If you just run the query you will understand what I am trying to say.
如果您只是运行查询，您就会明白我想说什么。
回答by cloakedninjas
Continuing from @Pablo pipes weren't concatenating for me - and I wanted to restrict it to a single database and then only tables
从@Pablo 管道继续对我来说并没有连接 - 我想将其限制为单个数据库，然后仅限制为表
SELECT CONCAT('truncate table ',table_name,';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<<YOUR-DB-NAME>>'
AND TABLE_TYPE = 'BASE TABLE';
回答by Cristiana Pereira
You can simply select all tables and then select truncate all.
您可以简单地选择所有表，然后选择全部截断。
回答by teteArg
Here I leave you a stored procedure to reset your database to cero
在这里，我给您留下了一个将数据库重置为 cero 的存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `reset_database`(DB_NAME VARCHAR(100))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tableName VARCHAR(100);
DECLARE cur CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema = DB_NAME AND table_type = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
SET FOREIGN_KEY_CHECKS = 0;
read_loop: LOOP
FETCH cur INTO tableName;
IF done THEN LEAVE read_loop; END IF;
SET @s = CONCAT('truncate table ',tableName);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END LOOP;
SET FOREIGN_KEY_CHECKS = 1;
CLOSE cur;
END;
回答by Dogbert
Not possible using a single sql query.
无法使用单个 sql 查询。
回答by codejunkie
There might be a situation that the truncation fails due to foreign key constraints. If you would still like to force truncation then you can select all tables for truncation as suggested by Cristiana Pereira.
由于外键约束，可能会出现截断失败的情况。如果您仍想强制截断，则可以按照 Cristiana Pereira 的建议选择所有表进行截断。
Then, click "Review SQL" instead of clicking on "Truncate" in the following window. Follow these steps
然后，单击“Review SQL”，而不是在接下来的窗口中单击“Truncate”。按着这些次序
- Copy all queries from the dialog box and then close it.
- click on "Create a new SQL tab for executing queries".
- Paste all queries between these 2 lines and execute.
- 从对话框中复制所有查询，然后关闭它。
- 单击“创建用于执行查询的新 SQL 选项卡”。
- 在这两行之间粘贴所有查询并执行。
set foreign_key_checks = 0;
set foreign_key_checks = 1;
设置foreign_key_checks = 0;
设置foreign_key_checks = 1;
