java - 让SQLite删除表中的所有记录

Question

我正在尝试删除位于我的 C:/驱动器中的数据库中 MAIN_TABLE 中的所有记录。我正在使用 SQLite 而我没有在 Android 平台上。我只看到几个示例中使用了 TURNCATE，所以我不能 100% 确定这是正确的..

代码:

package Exporter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DeleteAllRecords { 
//public void   runDeleteAllRecords()throws Exception, SQLException {
  public static void main(String[] argv) throws Exception {

String driverName = "org.sqlite.JDBC";
Class.forName(driverName);

String url = ("jdbc:sqlite:" + "C:/Test/DATABASE.db");
Connection connection = DriverManager.getConnection(url);
Statement stmt = connection.createStatement();

String sql = "TRUNCATE MAIN_TABLE";
stmt.executeUpdate(sql);
sql = "DELETE FROM MAIN_TABLE";
stmt.executeUpdate(sql);
}
}

错误:

Exception in thread "main" java.sql.SQLException: near "TRUNCATE": syntax error
at org.sqlite.core.NativeDB.throwex(NativeDB.java:397)
at org.sqlite.core.NativeDB._exec(Native Method)
at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:116)
at Exporter.DeleteAllRecords.main(DeleteAllRecords.java:21)

Answer 1

SQLite 没有实际的 TRUNCATE 命令。相反，它有一个截断优化器，只要您在没有 WHERE 子句的情况下运行 DELETE ，就会使用该优化器。所以你应该只使用 DELETE FROM 而没有 WHERE 子句来截断你的表:

String sql = "DELETE FROM MAIN_TABLE";
stmt.executeUpdate(sql);

来自documentation :

The Truncate Optimization

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.