MySQL 如何在不删除数据库的情况下在 phpMyAdmin 中删除数据库中所有表的内容？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2529763/
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 can I delete the contents of all tables in my database in phpMyAdmin without dropping the database?
提问by Joshua
How can I empty the contents of all tables in my database in phpMyAdmin without dropping any of the tables in the database?
如何在不删除数据库中的任何表的情况下清空 phpMyAdmin 中数据库中所有表的内容？
Since I do this several times an hour while in development, I'd rather not individually click "Empty" on all 60+ tables every time.
由于我在开发过程中每小时执行几次此操作，因此我不想每次都在所有 60 多个表上单独单击“空”。
采纳答案by Leniel Maccaferri
Create a SQL script with multiple DELETE statements (one for each table) and execute it.
使用多个 DELETE 语句（每个表一个）创建一个 SQL 脚本并执行它。
Get into phpMyAdmin and select the database that you want. Select the SQL tab and paste the SQL script into the window. Hit Go.
进入 phpMyAdmin 并选择所需的数据库。选择 SQL 选项卡并将 SQL 脚本粘贴到窗口中。点击去。
Look here too:
也看这里：
回答by andreas
IMHO the easiest solution is:
恕我直言，最简单的解决方案是：
- In phpmyadmin select the database you want to truncate in the databases-list (left-hand-side)
- In the main view scroll down, you'll see a checkbox "Check All" and next to it a drop-down box where you can select "Empty". Done.
- 在 phpmyadmin 中，在数据库列表（左侧）中选择要截断的数据库
- 在主视图中向下滚动，您会看到一个复选框“全选”，旁边有一个下拉框，您可以在其中选择“空”。完毕。
回答by John Yin
We can truncate all tables data by phpMyAdmin actually!
我们实际上可以通过phpMyAdmin截断所有表数据！
In phpMyAdmin, you can do it as following steps:
在 phpMyAdmin 中，您可以按照以下步骤进行操作：
1) select u DB and do Export operation as this way:
1) 选择 u DB 并按如下方式进行导出操作：
select Custom Export method
选择自定义导出方法
- select 'Dump Table -> data' in Format-specific options
- select 'Add DROP TABLE ... statement' in Object Creation Options.
- 在特定于格式的选项中选择“转储表 -> 数据”
- 在对象创建选项中选择“添加 DROP TABLE ... 语句”。
By this step, phpMyAdmin helps us create one sql script of full list of all tables
通过这一步，phpMyAdmin 帮助我们创建了一个所有表的完整列表的 sql 脚本
3) do Import operation to delete and create each blank table one by one by this script
3) 做Import操作，通过这个脚本一一删除和创建每个空表
回答by LesterDove
you could start with this query
你可以从这个查询开始
SELECT T.*
FROM INFORMATION_SCHEMA.tables T
WHERE T.table_type = 'BASE TABLE'
And iterate through those results to build a dynamic SQL string to the tune of 'DELETE FROM ' + T.Table_Name
并遍历这些结果以构建动态 SQL 字符串以调整 'DELETE FROM ' + T.Table_Name
回答by PatrikAkerstrand
Unfortunately, there is no TRUNCATE DATABASEor equivalent. With that said, you could probably use some kind of stored procedure that go through all tables in your database and truncate them. I found something of that kind here, but I don't know whether it works. (You should probably read the comment discussion too)
不幸的是，没有TRUNCATE DATABASE或等效的。话虽如此，您可能可以使用某种存储过程来遍历数据库中的所有表并截断它们。我在这里找到了那种东西，但我不知道它是否有效。（您也应该阅读评论讨论）
回答by mathijsuitmegen
The TRUNCATE TABLEstatement will empty a table completely (MySql 3.23 and above).
该TRUNCATE TABLE语句将完全清空表（MySql 3.23 及更高版本）。
You can create a script that you can reuse for a particular database. It's not generic but saves you from creating a new database + login each time you want to start with a fresh db.
您可以创建可重复用于特定数据库的脚本。它不是通用的，但可以避免每次要从新数据库开始时创建新数据库 + 登录名。
Here's how to create a script yourself:
In your webbrowser go to phpMyAdmin and select the database. Click the SQL tab and execute 'SHOW TABLES'.
Choose the printerfriendly format and copy the contents to a text editor.
以下是自己创建脚本的方法： 在您的网络浏览器中，转到 phpMyAdmin 并选择数据库。单击 SQL 选项卡并执行'SHOW TABLES'。选择适合打印的格式并将内容复制到文本编辑器。
Now for each table name you re-write it in the format:
现在，对于每个表名，您都将其重写为以下格式：
TRUNCATE TABLE <tablename>;
note: You can use a regular expression if you have many tables
注意：如果你有很多表，你可以使用正则表达式
回答by Byte2c
You can delete all data from phpmyadmin function easily. Maybe this feature didn't exists during 2010 when this question was posted, but I feel all beginners can refer to this.
您可以轻松地从 phpmyadmin 功能中删除所有数据。也许这个功能在2010年发布这个问题时不存在，但我觉得所有初学者都可以参考这个。
回答by josh kobrin
You could export the table structures only like this:
您只能像这样导出表结构：
mysqldump -u root -p --no-data mydb > backup_info.sql
then drop the whole database, recreate it and restore from the backup.
然后删除整个数据库，重新创建它并从备份中恢复。
Easy enough to script the whole thing.
很容易编写整个事情的脚本。
回答by Jon Black
drop procedure if exists truncate_tables;
delimiter #
create procedure truncate_tables()
begin
declare tab_name varchar(64);
declare done tinyint unsigned default 0;
declare table_cur cursor for select t.table_name
from
information_schema.schemata s
inner join information_schema.tables t on s.schema_name = t.table_schema
where
s.schema_name = database() and t.table_type = 'BASE TABLE';
declare continue handler for not found set done = 1;
open table_cur;
repeat
fetch table_cur into tab_name;
set @cmd = concat('truncate table ', tab_name);
prepare stmt from @cmd;
execute stmt;
until done end repeat;
close table_cur;
end #
回答by UnstableFractal
Actually, went deeper and wrote this:
实际上，更深入地写了这个：
SET @command:='';
SELECT @command:=CONCAT(@command, 'TRUNCATE TABLE ',T.TABLE_NAME,';')
FROM INFORMATION_SCHEMA.tables T
WHERE T.table_type = 'BASE TABLE' AND T.table_schema='YOUR_TABLE_SCHEMA';
PREPARE bye_world FROM @command;
EXECUTE bye_world;
DEALLOCATE PREPARE bye_world;
It selects all table names from provided schema YOUR_TABLE_SCHEMAand puts them into a @commanduser variable, forming a query for each one like this: TRUNCATE TABLE TABLE_NAME;
它从提供的模式中选择所有表名YOUR_TABLE_SCHEMA并将它们放入@command用户变量中，为每个表形成一个查询，如下所示：TRUNCATE TABLE TABLE_NAME;
Then i just prepare selected statement and execute it. Note, that you must declare user variable before query, or it will be 0and mess up our statement.
然后我只准备选定的语句并执行它。请注意，您必须在查询之前声明用户变量，否则它会0弄乱我们的语句。
Using MySQL DROP TABLE To Remove Existing Tables
使用 MySQL DROP TABLE 删除现有表
