MySQL 删除MYSQL中所有表的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1885101/
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
Delete data from all tables in MYSQL
提问by TIMEX
I have 100 tables, 40,000 rows in each table. I want to go into MySQL and delete allrows from alltables.
我有 100 个表，每个表中有 40,000 行。我想进入 MySQL 并删除所有表中的所有行。
...in 1 statement, if possible?
...在 1 个语句中，如果可能？
I want to keep the database and tables.
我想保留数据库和表。
采纳答案by paxdiablo
I don't think so (but I've been wrong before). What I tend to do is those cases is a two-step process.
我不这么认为（但我以前错了）。我倾向于做的是这些案例是一个两步过程。
If your DBMS has a command line interface, you can use it to create a script to do the bulk of the work, something like:
如果您的 DBMS 有命令行界面，您可以使用它来创建脚本来完成大部分工作，例如：
db2 "select 'db2 delete from ' | tblname from sysibm.systables
where owner = 'pax'" >p2.sh
p2.sh
The first bit simply creates a p2.shfile (or a p2.cmdfile under Windows) containing a delete fromstatement for every table owned by pax. Then you just run that command file to do the dirty work. You may want to check it first, of course :-)
第一位简单地创建一个p2.sh文件（或p2.cmd包含一个Windows下的文件），delete from由拥有每个表的语句pax。然后你只需运行该命令文件来完成肮脏的工作。当然，您可能想先检查一下:-)
Not the one-step process you were looking for but still very simple. I'm assuming here that mysql also has a command line interface.
不是您正在寻找的一步过程，但仍然非常简单。我在这里假设 mysql 也有一个命令行界面。
Update:
更新：
The MySQL version of the above looks like it should be:
上面的 MySQL 版本看起来应该是：
echo "select 'mysql truncate table ' | table_name
from information_schema.tables" | mysql >p2.sh
bash p2.sh
This uses the truncatecommand which is usually more efficient than delete fromfor deleting all rows. It also uses the proper MySQL system tables to get the table names.
这使用truncate通常比delete from删除所有行更有效的命令。它还使用正确的 MySQL 系统表来获取表名。
One point though - you may want to put a whereclause on that select to limit the tables to those you want deleted. The query as it stands will try to delete every table. One possibility is to limit it with specific table_schemaand/or table_typevalues.
但有一点 - 您可能希望where在该选择上放置一个子句以将表限制为您要删除的表。目前的查询将尝试删除每个表。一种可能性是用特定的table_schema和/或table_type值来限制它。
回答by zen
Easiest method to truncate all tables while retaining schema.
在保留架构的同时截断所有表的最简单方法。
mysqldump -d -uuser -ppass --add-drop-table databasename > databasename.sql
mysql -uuser -ppass databasename < databasename.sql
Not sure if it will retain stored procedures as they are not in use where I work, but I use this regularly to reset databases.
不确定它是否会保留存储过程，因为它们不在我工作的地方使用，但我经常使用它来重置数据库。
The -dswitch on mysqldump means "don't dump data."
-dmysqldump 上的开关意味着“不转储数据”。
The --add-drop-tableprepends a DROP TABLE statement to every CREATE TABLE in the dump.
在--add-drop-table预先设置成DROP TABLE语句的每一个垃圾堆CREATE TABLE。
回答by Mr. Deathless
The most robust way to clear all tables in a database was submitted by kostanosin How to remove all MySQL tables from the command-line without DROP database permissions?
最强大的清除数据库中所有表的方法是由kostanos在如何在没有 DROP 数据库权限的情况下从命令行中删除所有 MySQL 表中提交的？
Since the code below clears all tables in the current database you might want to select a different database before you proceed.
由于下面的代码清除了当前数据库中的所有表，因此您可能希望在继续之前选择一个不同的数据库。
USE DATABASE_YOU_WANT_TO_CLEAR;
Following snippet will remove data from the tables even in presence of foreign key constraints. You might also want to double check the list of tables before actual cleanup, just in case if you forgot to select proper database.
即使存在外键约束，以下代码段也会从表中删除数据。您可能还想在实际清理之前仔细检查表列表，以防万一您忘记选择正确的数据库。
-- save current foreign key settings and disable foreign key checks
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET GROUP_CONCAT_MAX_LEN=32768; -- you never know how people name their tables
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE()); -- operates on the current DB
SELECT IFNULL(@tables,'dummy') INTO @tables; -- avoid error if there are no tables
-- At this point you might want to double check the list of tables
-- to be cleared. Run SELECT @tables; to do so.
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
回答by error-driven-dev
In command line...
在命令行...
USE dbname
SET foreign_key_checks = 0;
TRUNCATE tablename; //do this for each table you want emptied
SET foreign_key_checks = 1;
回答by Taylor Leese
This will require a stored procedure or script that loops through each table and does:
这将需要一个循环遍历每个表并执行以下操作的存储过程或脚本：
truncate table <tablename>
truncate table <tablename>
To get the list of tables to truncate you can do something like:
要获取要截断的表列表，您可以执行以下操作：
SELECT table_name
FROM INFORMATION_SCHEMA.tables
WHERE table_schema = 'db_name'
回答by Francisco Aquino
Export the SQL script, delete the database, recreate the database against the script. :)
导出 SQL 脚本，删除数据库，根据脚本重新创建数据库。:)
回答by mzhang
$ mysqldump --no-data -u [username] -p[password] [database] > [location]
$ mysql -u [username] -p[password]
mysql > drop database [database]; create database [database];
mysql > exit;
$ mysql -u [username] -p[password] [database] < [location]
The --no-dataswitch preserves only the database table information, and will ignore all table data; you merely have to reimport the generated .sqlfile to regain all table information.
该--no-data开关仅保留数据库表的信息，并会忽略所有表数据; 您只需重新导入生成的.sql文件即可重新获得所有表信息。
回答by Jürgen Steinblock
I had to restore a backup which was missing the create statements. So I restored an older backup but I had to get rid of the data. This oneliner helped me truncate everything.
我不得不恢复一个缺少 create 语句的备份。所以我恢复了一个较旧的备份，但我不得不删除数据。这个oneliner帮助我截断了所有内容。
mysql -u user -ppass -NBe "SELECT 'SET foreign_key_checks = 0;' UNION SELECT CONCAT('TRUNCATE TABLE myschema.', table_name, ';') FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'myschema' AND table_type = 'BASE TABLE'" | mysql -u user -ppass
For better readability again with line breaks:
使用换行符再次获得更好的可读性：
mysql -u user -ppass -NBe "SELECT 'SET foreign_key_checks = 0;'
UNION SELECT CONCAT('TRUNCATE TABLE myschema.', table_name, ';')
FROM INFORMATION_SCHEMA.tables
WHERE table_schema = 'myschema' AND table_type = 'BASE TABLE'"
| mysql -u user -ppass
be sure to use the string without line breaks and replace myschemawith your table schema and set the correct login credentials or host for mysql process.
确保使用没有换行符的字符串并替换myschema为您的表架构，并为 mysql 进程设置正确的登录凭据或主机。
回答by Machoo
DELIMITER $$
DROP PROCEDURE IF EXISTS `cleanAllTables`$$
CREATE DEFINER=`root`@`%` PROCEDURE `cleanAllTables`()
BEGIN
DROP Temporary TABLE IF EXISTS AllTables;
Create Temporary Table AllTables (
SELECT @curRow := @curRow + 1 AS row_number
, table_name
FROM INFORMATION_SCHEMA.tables s
JOIN (SELECT @curRow := 0
) r
WHERE s.table_schema = 'databasename');
set @countOfAllTables = (select count(*) from AllTables);
set @c = 1;
WHILE @c<=@countOfAllTables DO
set @table_name = (select table_name from AllTables where row_number = @c);
set @stmt = concat( 'Truncate Table ', @table_name);
Prepare stmt from @stmt;
Execute stmt;
SET @c = @c + 1 ;
END WHILE ;
END$$
DELIMITER ;
回答by carlosayam
This one works for me in MySQL 5, and it is specific to tables:
这个在 MySQL 5 中对我有用，它特定于表：
echo 'show tables' | mysql --skip-column-names -u root YOUR_DB | awk '{print "truncate table " $0 ";"}' | mysql -u root YOUR_DB
echo 'show tables' | mysql --skip-column-names -u root YOUR_DB | awk '{print "truncate table " $0 ";"}' | mysql -u root YOUR_DB
Replace YOUR_DB by the name of your database. You have to provide your password twice, so you have a chance to think it again ... ;-)
将 YOUR_DB 替换为您的数据库名称。您必须提供两次密码，以便您有机会再次考虑...... ;-)
