SQL 删除所有表命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/525512/
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
Drop all tables command
提问by alamodey
What is the command to drop all tables in SQLite?
删除SQLite中所有表的命令是什么？
Similarly I'd like to drop all indexes.
同样，我想删除所有索引。
采纳答案by alamodey
rm db/development.sqlite3
回答by paxdiablo
I don't think you can drop all tables in one hit but you can do the following to get the commands:
我不认为您可以一次性删除所有表，但您可以执行以下操作来获取命令：
select 'drop table ' || name || ';' from sqlite_master
where type = 'table';
The output of this is a script that will drop the tables for you. For indexes, just replace table with index.
此输出是一个脚本，它将为您删除表。对于索引，只需用索引替换表。
You can use other clauses in the wheresection to limit which tables or indexes are selected (such as "and name glob 'pax_*'" for those starting with "pax_").
您可以使用该where部分中的其他子句来限制选择哪些表或索引（例如，and name glob 'pax_*'对于那些以“pax_”开头的表或索引）。
You could combine the creation of this script with the running of it in a simple bash (or cmd.exe) script so there's only one command to run.
您可以将此脚本的创建与它在一个简单的 bash（或 cmd.exe）脚本中的运行结合起来，以便只运行一个命令。
If you don't care about anyof the information in the DB, I think you can just delete the file it's stored in off the hard disk - that's probably faster. I've never tested this but I can't see why it wouldn't work.
如果您不关心数据库中的任何信息，我认为您可以删除存储在硬盘中的文件 - 这可能更快。我从来没有测试过这个，但我不明白为什么它不起作用。
回答by Noah
While it is true that there is no DROP ALL TABLES command you can use the following set of commands.
虽然确实没有 DROP ALL TABLES 命令，但您可以使用以下命令集。
Note:These commands have the potential to corrupt your database, so make sure you have a backup
注意：这些命令有可能损坏您的数据库，因此请确保您有备份
PRAGMA writable_schema = 1;
delete from sqlite_master where type in ('table', 'index', 'trigger');
PRAGMA writable_schema = 0;
you then want to recover the deleted space with
然后你想恢复已删除的空间
VACUUM;
and a good test to make sure everything is ok
以及确保一切正常的良好测试
PRAGMA INTEGRITY_CHECK;
回答by it-west.net
I had the same problem with SQLite and Android. Here is my Solution:
我在 SQLite 和 Android 上遇到了同样的问题。这是我的解决方案：
List<String> tables = new ArrayList<String>();
Cursor cursor = db.rawQuery("SELECT * FROM sqlite_master WHERE type='table';", null);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
String tableName = cursor.getString(1);
if (!tableName.equals("android_metadata") &&
!tableName.equals("sqlite_sequence"))
tables.add(tableName);
cursor.moveToNext();
}
cursor.close();
for(String tableName:tables) {
db.execSQL("DROP TABLE IF EXISTS " + tableName);
}
回答by Flavio Tordini
I'd like to add to other answers involving dropping tables and not deleting the file, that you can also execute delete from sqlite_sequenceto reset auto-increment sequences.
我想添加其他涉及删除表而不删除文件的答案，您也可以执行这些delete from sqlite_sequence来重置自动递增序列。
回答by user3467349
Using pysqlite:
使用 pysqlite：
tables = list(cur.execute("select name from sqlite_master where type is 'table'"))
cur.executescript(';'.join(["drop table if exists %s" %i for i in tables]))
回答by Jon
I had this issue in Android and I wrote a method similar to it-west.
我在 Android 中遇到了这个问题，我写了一个类似于 it-west 的方法。
Because I used AUTOINCREMENTprimary keys in my tables, there was a table called sqlite_sequence. SQLite would crash when the routine tried to drop that table. I couldn't catch the exception either. Looking at https://www.sqlite.org/fileformat.html#internal_schema_objects, I learned that there could be several of these internal schematables that I didn't want to drop. The documentation says that any of these tables have names beginning with sqlite_so I wrote this method
因为我AUTOINCREMENT在我的表中使用了主键，所以有一个名为sqlite_sequence. 当例程试图删除该表时，SQLite 会崩溃。我也无法捕获异常。查看https://www.sqlite.org/fileformat.html#internal_schema_objects，我了解到可能有几个我不想删除的内部架构表。文档说这些表中的任何一个都有以sqlite_开头的名称，所以我写了这个方法
private void dropAllUserTables(SQLiteDatabase db) {
Cursor cursor = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
//noinspection TryFinallyCanBeTryWithResources not available with API < 19
try {
List<String> tables = new ArrayList<>(cursor.getCount());
while (cursor.moveToNext()) {
tables.add(cursor.getString(0));
}
for (String table : tables) {
if (table.startsWith("sqlite_")) {
continue;
}
db.execSQL("DROP TABLE IF EXISTS " + table);
Log.v(LOG_TAG, "Dropped table " + table);
}
} finally {
cursor.close();
}
}
回答by Mike Woodhouse
Once you've dropped all the tables (and the indexes will disappear when the table goes) then there's nothing left in a SQLite database as far as I know, although the file doesn't seem to shrink (from a quick test I just did).
一旦你删除了所有的表（当表消失时索引会消失），就我所知，SQLite 数据库中没有任何东西，尽管文件似乎没有缩小（从我刚刚做的快速测试中） ）。
So deleting the file would seem to be fastest - it should just be recreated when your app tries to access the db file.
所以删除文件似乎是最快的——它应该在你的应用程序尝试访问 db 文件时重新创建。
回答by Matt Malone
I can't say this is the most bulletproof or portable solution, but it works for my testing scripts:
我不能说这是最防弹或最便携的解决方案，但它适用于我的测试脚本：
.output /tmp/temp_drop_tables.sql
select 'drop table ' || name || ';' from sqlite_master where type = 'table';
.output stdout
.read /tmp/temp_drop_tables.sql
.system rm /tmp/temp_drop_tables.sql
This bit of code redirects output to a temporary file, constructs the 'drop table' commands that I want to run (sending the commands to the temp file), sets output back to standard out, then executes the commands from the file, and finally removes the file.
这段代码将输出重定向到一个临时文件，构建我想要运行的“删除表”命令（将命令发送到临时文件），将输出设置回标准输出，然后执行文件中的命令，最后删除文件。
回答by peak
Or at a shell prompt, in just two lines, without a named temporary file, assuming $db is the SQLite database name:
或者在 shell 提示符下，仅两行，没有命名的临时文件，假设 $db 是 SQLite 数据库名称：
echo "SELECT 'DROP TABLE ' || name ||';' FROM sqlite_master WHERE type = 'table';" |
sqlite3 -readonly "$db" | sqlite3 "$db"
