php 如何删除数据库中的所有表而不删除数据库本身？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3493253/
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 drop all tables in database without dropping the database itself?
提问by Misha Moroshko
I would like to delete all the tables from database, but not deleting the database itself. Is it possible ? I'm just looking for shorter way than removing the database and create it again. Thanks !
我想从数据库中删除所有表，但不删除数据库本身。是否可以 ？我只是在寻找比删除数据库并重新创建它更短的方法。谢谢 ！
回答by Tomasz Struczyński
The shortest is to re-create database. but if you don't want to...
最短的是重新创建数据库。但如果你不想...
This is for MySQL/PHP. Not tested but something like that.
这是用于 MySQL/PHP。没有测试，但类似的东西。
$mysqli = new mysqli("host", "my_user", "my_password", "database");
$mysqli->query('SET foreign_key_checks = 0');
if ($result = $mysqli->query("SHOW TABLES"))
{
while($row = $result->fetch_array(MYSQLI_NUM))
{
$mysqli->query('DROP TABLE IF EXISTS '.$row[0]);
}
}
$mysqli->query('SET foreign_key_checks = 1');
$mysqli->close();
回答by fredley
There is no simple way to do this. Either you'll need to know what the tables are in advance:
没有简单的方法可以做到这一点。要么你需要提前知道表格是什么：
//edit you can get this information using the query SHOW TABLE STATUS
//编辑您可以使用查询获取此信息 SHOW TABLE STATUS
$tables = array('users','otherdata');
foreach($tables as $table){
db.execute("DROP TABLE "+$table);
}
or you can drop the database and re-create it empty (it's really not that much effort!):
或者您可以删除数据库并将其重新创建为空（这真的没有那么多努力！）：
db.execute('DROP DATABASE SITEDATA');
db.execute('CREATE DATABASE SITEDATA');
回答by wimvds
You'd have to drop every table in the db separately, so dropping the database and recreating it will actually be the shortest route (and the fastest one for that matter).
您必须分别删除数据库中的每个表，因此删除数据库并重新创建它实际上是最短的路线（也是最快的路线）。
回答by Brett McCann
When I had to do this in Oracle, I would write a select statement that would generate the drop table statements for me. Something to the effect of:
当我必须在 Oracle 中执行此操作时，我会编写一个 select 语句，该语句将为我生成 drop table 语句。有以下作用：
Select 'DROP TABLE ' || table_name || ';' from user_tables;
选择“删除表” || 表名 || ';' 来自用户表；
I could then pipe the output of the select statement to a file. After I ran this, I would have a file that would drop all my tables for me. It would look something like:
然后我可以将 select 语句的输出通过管道传输到一个文件。运行此程序后，我将有一个文件可以为我删除所有表。它看起来像：
DROP TABLE TABLE1;
删除表TABLE1;
DROP TABLE TABLE2;
删除表TABLE2;
DROP TABLE TABLE3;
删除表TABLE3;
etc...
等等...
Not a mysql expert, but I would imagine it would have a similar facility to both select all tables for a schema, as well as direct output from a SQL statement to a file.
不是 mysql 专家，但我想它会有类似的功能来选择模式的所有表，以及从 SQL 语句直接输出到文件。
回答by IcanDivideBy0
Use SHOW TABLE STATUSto get all tables in your database, then loop over result and drop them one by one.
使用SHOW TABLE STATUS让所有的表在数据库中，然后在结果循环，并通过一个拖放一个。
回答by Vladislav Rastrusny
There are some solutions here in comments: http://dev.mysql.com/doc/refman/5.1/en/drop-table.html
评论中有一些解决方案：http: //dev.mysql.com/doc/refman/5.1/en/drop-table.html
回答by Abdul
I needed to drop all tables except a couple from an inadvertent dump.
我需要删除所有表，除了无意中的转储中的一对表。
A PHP function to drop all tables except some (adapted from here), for anyone else who might need:
一个 PHP 函数，用于删除除某些表之外的所有表（改编自此处），供其他可能需要的人使用：
<?php
$mysqli = new mysqli( "localhost", "user", 'password', "database");
function drop_all_tables($exceptions_array, $conn) {
$exceptions_string="('" ;
foreach ($exceptions_array as $table) {
$exceptions_string .=$table . "','";
}
$exceptions_string=rtrim($exceptions_string, ",'");
$exceptions_string .="')" ;
$sql="SELECT CONCAT('DROP TABLE ', TABLE_NAME, '; ')
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name NOT IN $exceptions_string";
$result=$ conn->query($sql);
while($row = $result->fetch_array(MYSQLI_NUM)) {
$conn->query($row[0]);
}
}
//drop_all_tables(array("table1","table2","table3","table4"), $mysqli);
?>
回答by user9453341
The single line query to drop all tables, as below:
删除所有表的单行查询，如下所示：
$dbConnection = mysqli_connect("hostname", "username", "password", "database_name");
$dbConnection->query('SET foreign_key_checks = 0');
$qry_drop = "DROP TABLE IF EXISTS buildings, business, computer, education, fashion, feelings, food, health, industry, music, nature, people, places, religion, science, sports, transportation, travel";
$dbConnection->query($qry_drop);
$mysqli->query('SET foreign_key_checks = 1');
$mysqli->close();
回答by Abz
You can execute this. Just add more tables if I missed any
你可以执行这个。如果我错过了任何桌子，只需添加更多桌子
drop table wp_commentmeta;
drop table wp_comments;
drop table wp_links;
drop table wp_options;
drop table wp_postmeta;
drop table wp_posts;
drop table wp_term_relationships;
drop table wp_term_taxonomy;
drop table wp_termmeta;
drop table wp_terms;
drop table wp_usermeta;
drop table wp_users;
