我想从表中删除所有内容并重置自动递增主键。我这样做：
@Query("delete from sqlite_sequence where name='bin';")
void delete();
@Query("DELETE FROM bin")
void nukeTable();
@Query("UPDATE SQLITE_SEQUENCE SET seq = 1 WHERE name = 'bin';")
void resetPrimaryKey();
但它不起作用
最佳答案
您的问题是room防止使用sqlite表，即那些以sqlite开始的表，因此您不能在room中直接重置sqlite顺序。
下面是两种避免这种情况的方法，以实现在房间内重置序列的目标。第三个选项（未显示）是关闭文件室数据库，然后使用sqlitedatabase实例访问数据库以重置序列。
方案A
可以通过删除表，然后使用supportsqlitedatabase实例重新创建它来完成此操作。
以下是选项A的演示（但也包括不必要地关闭文件室数据库，以及在文件室对象之外使用数据库，但是这是通过从sqlite_序列中提取数据作为技术证明而添加的）。
注意
上面的代码只是技术的一个演示，上面的代码在room和non-room之间交换存在一些问题，这是解决方案所不需要的。
核心测试代码：
//Stage 1 load some data
getRoomDB();
mRowIdTestDao = mRTDB.rowIdTestDao();
mRowIdTestDao.insertManyRowIdTests(
new RowIdTest("A"),
new RowIdTest("B"),
new RowIdTest("C")
);
// Stage 2 close to room and dump sqlite_sequence
//mRTDB.close();
getNonRoomDB();
DatabaseUtils.dumpCursor(mNotRoomdb.query("sqlite_sequence",null,null,null,null,null,null));
mNotRoomdb.close();
//Stage 3 clear sqlite_sequence by dropping the table using SupportSQLiteDatabase
getRoomDB();
mSuppDB.execSQL("DROP TABLE RowIdTest"); //<<<<<<<<<< DROP THE TABLE
mSuppDB.execSQL("CREATE TABLE IF NOT EXISTS RowIdTest (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)"); //<<<<<<<<<< CREATE THE TABLE
// Stage 4 close to room and dump sqlite_sequence
mRTDB.close();
getNonRoomDB();
DatabaseUtils.dumpCursor(mNotRoomdb.query("sqlite_sequence",null,null,null,null,null,null));
mNotRoomdb.close();
// Stage 5 check that all is OK again (add some rows)
getRoomDB();
mRowIdTestDao.insertManyRowIdTests(
new RowIdTest("A"),
new RowIdTest("B"),
new RowIdTest("C")
);
DatabaseUtils.dumpCursor(mSuppDB.query("SELECT * FROM RowIdTest"));
调用的方法：
RoomDatabase.Callback getSuppDb = new RoomDatabase.Callback() {
@Override
public void onOpen(@NonNull SupportSQLiteDatabase db) {
super.onOpen(db);
mSuppDB = db;
}
};
private void getRoomDB() {
mRTDB = Room.databaseBuilder(this,RoomTestingDatabase.class,"rtdb.db")
.allowMainThreadQueries()
.addCallback(getSuppDb)
.build();
}
private void getNonRoomDB() {
mNotRoomdb = SQLiteDatabase.openDatabase(
this.getDatabasePath("rtdb.db").getPath(),
null,
SQLiteDatabase.OPEN_READWRITE
);
}
日志结果
2019-10-03 17:13:25.506 W/SQLiteConnection: Could not change the database journal mode of '/data/user/0/art.roomtesting/databases/rtdb.db' from 'wal' to 'TRUNCATE' because the database is locked. This usually means that there are other open connections to the database which prevents the database from enabling or disabling write-ahead logging mode. Proceeding without changing the journal mode.
2019-10-03 17:13:25.508 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@add2875
2019-10-03 17:13:25.508 I/System.out: 0 {
2019-10-03 17:13:25.508 I/System.out: name=RowIdTest
2019-10-03 17:13:25.508 I/System.out: seq=6
2019-10-03 17:13:25.508 I/System.out: }
2019-10-03 17:13:25.509 I/System.out: <<<<<
2019-10-03 17:13:25.511 E/SQLiteLog: (5) statement aborts at 1: [PRAGMA journal_mode=TRUNCATE] database is locked
2019-10-03 17:13:25.511 W/SQLiteConnection: Could not change the database journal mode of '/data/user/0/art.roomtesting/databases/rtdb.db' from 'wal' to 'TRUNCATE' because the database is locked. This usually means that there are other open connections to the database which prevents the database from enabling or disabling write-ahead logging mode. Proceeding without changing the journal mode.
2019-10-03 17:13:25.512 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@38ae98
2019-10-03 17:13:25.513 I/System.out: <<<<<
2019-10-03 17:13:25.518 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f873957
2019-10-03 17:13:25.518 I/System.out: 0 {
2019-10-03 17:13:25.518 I/System.out: id=1
2019-10-03 17:13:25.518 I/System.out: name=A
2019-10-03 17:13:25.518 I/System.out: }
2019-10-03 17:13:25.518 I/System.out: 1 {
2019-10-03 17:13:25.518 I/System.out: id=2
2019-10-03 17:13:25.518 I/System.out: name=B
2019-10-03 17:13:25.518 I/System.out: }
2019-10-03 17:13:25.518 I/System.out: 2 {
2019-10-03 17:13:25.518 I/System.out: id=3
2019-10-03 17:13:25.519 I/System.out: name=C
2019-10-03 17:13:25.519 I/System.out: }
2019-10-03 17:13:25.519 I/System.out: <<<<<
方案B
另一种方法是使用autoincrement创建表，因此rowid仅基于表中的行生成。我相信在不使用autoincrement方面存在一些混淆（或a）方法是不编写autogenerate=true并使用long-not-long（或integer-not-int作为类型，不建议使用）。
考虑上面使用的rowtestid表/实体，它是：
@Entity
public class RowIdTest {
@PrimaryKey(autoGenerate = true)
private long id;
private String name;
public RowIdTest() {};
@Ignore
public RowIdTest(String name){
this.name = name;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
等效的非自动增量版本为：
@Entity
public class AltRowIdTest {
@PrimaryKey
private Long id; //<<<<<<<<<< Long not long
private String name;
public AltRowIdTest() {};
@Ignore
public AltRowIdTest(String name){
this.name = name;
}
public Long getId() { //<<<<<<<<<< Long not long
return id;
}
public void setId(Long id) { //<<<<<<<<<< Long not long
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
还应考虑：
@Query("DELETE FROM AltRowIdTest")
int clearAllAltRowIdTestRows();
然后是以下代码：
//Option B
mAltRowIdTestDao.insertManyRowIdTests(new AltRowIdTest("X"),new AltRowIdTest("Y"),new AltRowIdTest("Z"));
DatabaseUtils.dumpCursor(mSuppDB.query("SELECT * FROM AltRowIdTest"));
mAltRowIdTestDao.clearAllAltRowIdTestRows();
mAltRowIdTestDao.insertManyRowIdTests(new AltRowIdTest("X"),new AltRowIdTest("Y"),new AltRowIdTest("Z"));
DatabaseUtils.dumpCursor(mSuppDB.query("SELECT * FROM AltRowIdTest"));
日志的结果是：
2019-10-03 17:49:31.783 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@561c44
2019-10-03 17:49:31.785 I/System.out: 0 {
2019-10-03 17:49:31.785 I/System.out: id=1
2019-10-03 17:49:31.785 I/System.out: name=A
2019-10-03 17:49:31.785 I/System.out: }
2019-10-03 17:49:31.785 I/System.out: 1 {
2019-10-03 17:49:31.785 I/System.out: id=2
2019-10-03 17:49:31.785 I/System.out: name=B
2019-10-03 17:49:31.785 I/System.out: }
2019-10-03 17:49:31.785 I/System.out: 2 {
2019-10-03 17:49:31.785 I/System.out: id=3
2019-10-03 17:49:31.785 I/System.out: name=C
2019-10-03 17:49:31.786 I/System.out: }
2019-10-03 17:49:31.786 I/System.out: <<<<<
2019-10-03 17:49:31.793 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f369662
2019-10-03 17:49:31.794 I/System.out: 0 {
2019-10-03 17:49:31.794 I/System.out: id=1
2019-10-03 17:49:31.794 I/System.out: name=X
2019-10-03 17:49:31.794 I/System.out: }
2019-10-03 17:49:31.794 I/System.out: 1 {
2019-10-03 17:49:31.794 I/System.out: id=2
2019-10-03 17:49:31.794 I/System.out: name=Y
2019-10-03 17:49:31.794 I/System.out: }
2019-10-03 17:49:31.794 I/System.out: 2 {
2019-10-03 17:49:31.794 I/System.out: id=3
2019-10-03 17:49:31.794 I/System.out: name=Z
2019-10-03 17:49:31.794 I/System.out: }
2019-10-03 17:49:31.795 I/System.out: <<<<<
即，id列在删除所有行后从1重新启动，或者与选项a不同，如果删除了末尾的行，则它将重新使用已释放的id（如果未删除结尾行，则不会）。此外，与autoincrement不同的是，如果达到最高id值（9223372036854775807）（原因id应始终为long或long，而不是int或integer），则不会导致sqlite_full异常，因为sqlite将尝试使用一个随机未使用的数（您还可以使用负值使可用范围几乎加倍）。
关于java - 清除表并重置自动增量主键，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58212703/