有关sqlitedrop数据库重建比delete方式来清空数据库更加有效率

今天浏览stackoverflow 发现一个有趣的问题：

which was more preferable as performance wise and without error cause to delete all the row from table in sqlite in android.

DELETE QUERY OR DROP TABLE AND RE-GENERATE THE TABLE？

及关于删除sqlite里面所有数据，用drop并重建比delete all sqlite里面的数据更加有效率

it is more efficient to drop table and re-create it; and yes, You can use "IF EXISTS" in this case

DELETE FROM will cause SQLite to visit individual rows unless those rows have triggers, so it‘s generally reasonably efficient.

edited after 1 answer post

As the Droping the table using drop table query then internally delete query was used? As in one answer post


"When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This "truncate" optimization makes the delete run much faster. Prior to SQLite version 3.6.5, the truncate optimization also meant that the sqlite3_changes() and sqlite3_total_changes() interfaces and the count_changes pragma will not actually return the number of deleted rows. That problem has been fixed as of version 3.6.5."

当忽略来自DELETE语句,表被删除没有触发,SQLite使用一个优化删除整个表内容无需单独访问表的每一行。 这种“截断”优化使得删除运行更快。 3.6.5 SQLite版本之前,截断优化也意味着sqlite3_changes()和sqlite3_total_changes()接口和count_changes编译指示实际上不会返回删除的行数。 这个问题已经固定的版本3.6.5。”

SQLite它说:默认的构建,如果一个DELETE语句没有WHERE子句和运营一个表没有触发,导致删除发生发生优化删除和重新创建表。 删除和重新创建一个表通常远远高于逐行删除表的内容。 这是“截断优化”
也就是delete有读取一行删除一行的，drop后创建明显效率高些