blocks|key|955140|text|最短的是重新创建数据库。但如果你不想..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|955141|这是针对MySQL/PHP的。没有经过测试，但类似的东西。|955142|$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();|code-block|syntax|javascript|955143|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>$mysqli = new mysqli("host", "my_user", "my_password", "database");
$mysqli-&gt;query('SET foreign_key_checks = 0');
if ($result = $mysqli-&gt;query("SHOW TABLES"))
{
 while($row = $result-&gt;fetch_array(MYSQLI_NUM))
 {
 $mysqli-&gt;query('DROP TABLE IF EXISTS '.$row[0]);
 }
}

$mysqli-&gt;query('SET foreign_key_checks = 1');
$mysqli-&gt;close();
</code></pre>

blocks|key|1169233|text|没有简单的方法可以做到这一点。要么你需要提前知道这些表是什么：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1169234|//edit您可以使用查询SHOW+TABLE+STATUS获取此信息|offset|length|style|CODE|1169235|$tables+=+array('users','otherdata');
foreach($tables+as+$table){
++db.execute("DROP+TABLE+"%2B$table);
}|code-block|syntax|javascript|1169236|或者，您可以删除数据库并重新创建它为空(这真的不是很费力！)：|1169237|db.execute('DROP+DATABASE+SITEDATA');
db.execute('CREATE+DATABASE+SITEDATA');|1169238|entityMap^0|0|D|H|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|W|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|Y|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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 <code>SHOW TABLE STATUS</code>

<pre><code>$tables = array('users','otherdata');
foreach($tables as $table){
 db.execute("DROP TABLE "+$table);
}
</code></pre>

or you can drop the database and re-create it empty (it's really not that much effort!):

<pre><code>db.execute('DROP DATABASE SITEDATA');
db.execute('CREATE DATABASE SITEDATA');
</code></pre>

blocks|key|955038|text|您必须分别删除数据库中的每个表，因此删除数据库并重新创建它实际上是最短的路径(也是最快的路径)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|955039|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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).

blocks|key|1169317|text|当我必须在Oracle中执行此操作时，我会编写一条select语句，该语句将为我生成drop+table语句。对…有影响的事物：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1169318|从user_tables；选择'DROP+TABLE‘%7C%7C+table_name+%7C%7C+';’|1169319|然后，我可以将select语句的输出通过管道传输到一个文件。在我运行这个之后，我会有一个文件，它将为我删除我所有的表。它看起来像这样：|1169320|DROP表TABLE1；|1169321|DROP表TABLE2；|1169322|DROP表TABLE3；|1169323|等等。|1169324|我不是mysql专家，但我想它应该有类似的功能，既可以为模式选择所有表，也可以将SQL语句直接输出到文件中。|1169325|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|V|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|W|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|Y|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|Q|$]]

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:

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:

DROP TABLE TABLE1;

DROP TABLE TABLE2;

DROP TABLE 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.

blocks|key|1169348|text|使用SHOW+TABLE+STATUS获取数据库中的所有表，然后循环结果并逐个删除它们。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1169349|entityMap^0|2|H|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Use <code>SHOW TABLE STATUS</code> to get all tables in your database, then loop over result and drop them one by one.

blocks|key|1169412|text|在评论中有一些解决方案：http://dev.mysql.com/doc/refman/5.1/en/drop-table.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1169413|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/drop-table.html^0|C|1I|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

There are some solutions here in comments: <a href="http://dev.mysql.com/doc/refman/5.1/en/drop-table.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.1/en/drop-table.html</a>

blocks|key|955223|text|我需要从一个无意的转储中删除除了几个表之外的所有表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|955224|一个PHP函数，用于删除除某些表(改编自here)之外的所有表，以供其他可能需要的人使用：|offset|length|955225|<?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);
?>|code-block|syntax|javascript|955226|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/1454392/3040381^0|0|K|4|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@$D|U|E|V|1|W]]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

I needed to drop all tables except a couple from an inadvertent dump.

A PHP function to drop all tables except some (adapted from <a href="https://stackoverflow.com/a/1454392/3040381">here</a>), for anyone else who might need:

<pre><code>&lt;?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-&gt;query($sql);
 while($row = $result-&gt;fetch_array(MYSQLI_NUM)) {
 $conn-&gt;query($row[0]);
 }
}

//drop_all_tables(array("table1","table2","table3","table4"), $mysqli);
?&gt;
</code></pre>

blocks|key|955304|text|执行此操作的程序性方法如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|955305|$query_disable_checks+=+'SET+foreign_key_checks+=+0';

$query_result+=+mysqli_query($connect,+$query_disable_checks);

//+Get+the+first+table
$show_query+=+'Show+tables';
$query_result+=+mysqli_query($connect,+$show_query);
$row+=+mysqli_fetch_array($query_result);

while+($row)+{
++$query+=+'DROP+TABLE+IF+EXISTS+'+.+$row[0];
++$query_result+=+mysqli_query($connect,+$query);

++//+Getting+the+next+table
++$show_query+=+'Show+tables';
++$query_result+=+mysqli_query($connect,+$show_query);
++$row+=+mysqli_fetch_array($query_result);
}|code-block|syntax|javascript|955306|这里，$connect只是与mysqli_connect();建立的连接。|offset|length|style|CODE|955307|entityMap^0|0|0|3|8|E|H|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

A procedural way to do this is as follows:
<pre><code>$query_disable_checks = 'SET foreign_key_checks = 0';

$query_result = mysqli_query($connect, $query_disable_checks);

// Get the first table
$show_query = 'Show tables';
$query_result = mysqli_query($connect, $show_query);
$row = mysqli_fetch_array($query_result);

while ($row) {
 $query = 'DROP TABLE IF EXISTS ' . $row[0];
 $query_result = mysqli_query($connect, $query);

 // Getting the next table
 $show_query = 'Show tables';
 $query_result = mysqli_query($connect, $show_query);
 $row = mysqli_fetch_array($query_result);
}
</code></pre>
Here <code>$connect</code> is just the connection made with <code>mysqli_connect();</code>.

blocks|key|1169477|text|删除所有表的单行查询，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1169478|$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();|code-block|syntax|javascript|1169479|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The single line query to drop all tables, as below:

<pre><code>$dbConnection = mysqli_connect("hostname", "username", "password", "database_name");
$dbConnection-&gt;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-&gt;query($qry_drop);

$mysqli-&gt;query('SET foreign_key_checks = 1');
$mysqli-&gt;close();
</code></pre>

blocks|key|955271|text|您可以执行此操作。如果遗漏了任何表格，只需添加更多表格即可|type|unstyled|depth|inlineStyleRanges|entityRanges|data|955272|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;|code-block|syntax|javascript|955273|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can execute this. Just add more tables if I missed any

<pre><code>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;
</code></pre>

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 !

How to drop all tables in database without dropping the database itself?

数据库

MySQL

我想从数据库中删除所有的表，但不删除数据库本身。有可能吗？我只是在寻找比删除数据库并重新创建它更短的方法。谢谢！

问如何在不删除数据库本身的情况下删除数据库中的所有表？
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不删除数据库本身的情况下删除数据库中的所有表？EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不删除数据库本身的情况下删除数据库中的所有表？
EN