为了在退出时清除表格,但是这不会重置键的起始索引,而是从上次运行结束时开始。
....
"delete from sqlite_sequence where name='Sequence Action'" 没有错误,但索引没有被重置。
您需要同时删除 SequenceAction 表中的所有行和从 sqlite_sequence 中删除相应的行。
当使用 AUTOINCREMENT 关键字时,将使用不同的算法。这是沿以下线路进行的:
找到以下内容的最高值
- a)表中存储的值在 sqlite_sequence 中的编号和
- b)最高 rowid 值
另一种选择是不使用 AUTOINCREMENT
关键字,而是只有 ?? INTEGER PRIMARY KEY
(其中 ?? 代表列名)。
您仍然会拥有一个唯一的 ID,它是 rowid
列的别名,但不能保证它总是增加的。 AUTOINCREMENT
可以保证递增的唯一 ID,但不能保证递增的唯一 rowid。
每次应用程序运行时,我需要此键从 0 开始。
但是,SQLite 将第一个值设置为 1 而不是 0。
以下代码是可以正常工作的,尽管使用了AUTOINCREMENT(这有点像一个技巧):
DROP TABLE IF EXISTS SequenceAction;
DROP TRIGGER IF EXISTS use_zero_as_first_sequence;
CREATE TABLE IF NOT EXISTS SequenceAction (id INTEGER PRIMARY KEY AUTOINCREMENT, otherdata TEXT);
CREATE TRIGGER IF NOT EXISTS use_zero_as_first_sequence AFTER INSERT ON SequenceAction
BEGIN
UPDATE SequenceAction SET id = id - 1 WHERE id = new.id;
END
;
INSERT INTO SequenceAction VALUES(null,'TEST1'),(null,'TEST2'),(null,'TEST3');
SELECT * FROM SequenceAction;
DELETE FROM SequenceAction;
DELETE FROM sqlite_sequence WHERE name = 'SequenceAction';
INSERT INTO SequenceAction VALUES(null,'TEST4'),(null,'TEST5'),(null,'TEST6');
SELECT * FROM SequenceAction
结果为:
第一个查询返回:
![enter image description here](https://istack.dev59.com/HE8b6.webp)
and the 2nd returning :-
![enter image description here](https://istack.dev59.com/I2toc.webp)
所以本质上你想要的是:
DELETE FROM SequenceAction;
DELETE FROM sqlite_sequence WHERE name = 'SequenceAction';
如果你想从0开始编号,可以使用触发器来实现。
或者,如果取消了AUTOINCREMENT,你可以使用略微修改的触发器:
CREATE TRIGGER IF NOT EXISTS use_zero_as_first_sequence
AFTER INSERT ON SequenceAction
WHEN (SELECT count() FROM SequenceAction) = 1
BEGIN
UPDATE SequenceAction SET id = 0;
END
;
- 这只是重新编号第一个插入的行(然后算法为后续插入添加1)。
然后仅从SequenceAction表中删除所有行,以重置编号。
使用Room的示例:
根据您的代码以及上面的示例,以下方法似乎有效:
private void resetSequenceAction() {
SQLiteDatabase dbx;
String sqlite_sequence_table = "sqlite_sequence";
long initial_sacount;
long post_sacount;
long initial_ssn =0;
long post_ssn = 0;
Cursor csr;
File f = this.getDatabasePath(TestDatabase.DBNAME);
if (!f.exists()) {
File d = new File(this.getDatabasePath(TestDatabase.DBNAME).getParent());
d.mkdirs();
dbx = SQLiteDatabase.openOrCreateDatabase(f,null);
String crtsql = "CREATE TABLE IF NOT EXISTS " + SequenceAction.tablename + "(" +
SequenceAction.id_column + " INTEGER PRIMARY KEY AUTOINCREMENT," +
SequenceAction.actionType_column + " TEXT," +
SequenceAction.extraInfo_column + " TEXT" +
")";
dbx.execSQL(crtsql);
String triggerSql = "CREATE TRIGGER IF NOT EXISTS user_zero_as_first_rowid AFTER INSERT ON " +
SequenceAction.tablename +
" BEGIN " +
" UPDATE " + SequenceAction.tablename +
" SET " +
SequenceAction.id_column + " = " + SequenceAction.id_column + " - 1 " +
" WHERE " + SequenceAction.id_column + " = new." + SequenceAction.id_column + ";" +
" END ";
dbx.execSQL(triggerSql);
} else {
dbx = SQLiteDatabase.openDatabase(this.getDatabasePath(TestDatabase.DBNAME).getPath(),null, Context.MODE_PRIVATE);
}
initial_sacount = DatabaseUtils.queryNumEntries(dbx,SequenceAction.tablename);
String deleteAllSequenceIdRowsSql = "DELETE FROM " + SequenceAction.tablename;
dbx.execSQL(deleteAllSequenceIdRowsSql);
post_sacount = DatabaseUtils.queryNumEntries(dbx,SequenceAction.tablename);
csr = dbx.query(sqlite_sequence_table,
new String[]{"seq"},"name=?",
new String[]{SequenceAction.tablename},
null,null,null
);
if (csr.moveToFirst()) {
initial_ssn = csr.getLong(csr.getColumnIndex("seq"));
}
String deleteSqlLiteSequenceRow = "DELETE FROM " +
sqlite_sequence_table +
" WHERE name = '" + SequenceAction.tablename + "'";
dbx.execSQL(deleteSqlLiteSequenceRow);
csr = dbx.query(
sqlite_sequence_table,
new String[]{"seq"},
"name=?",
new String[]{SequenceAction.tablename},
null,null,null
);
if (csr.moveToFirst()) {
post_ssn = csr.getLong(csr.getColumnIndex("seq"));
}
csr.close();
Log.d("SEQACTSTATS",
"Initial Rowcount=" + String.valueOf(initial_sacount) +
" Initial Seq#=" + String.valueOf(initial_ssn) +
" Post Delete Rowcount =" + String.valueOf(post_sacount) +
" Post Delete Seq#=" + String.valueOf(post_ssn)
);
dbx.close();
}
第一次运行的结果(即不存在数据库):
D/SEQACTSTATS: Initial Rowcount=0 Initial Seq#=0 Post Delete Rowcount =0 Post Delete Seq#=0
从后续的运行结果中(在添加了40行后):
D/SEQACTSTATS: Initial Rowcount=40 Initial Seq#=40 Post Delete Rowcount =0 Post Delete Seq#=0
添加一个方法以列出所有行,如下所示:
private void listAllRows() {
new Thread(new Runnable() {
@Override
public void run() {
salist = mTestDB.SequenceActionDaoAccess().getAll();
getSequenceActionList(salist);
}
}).start();
}
Along with :-
@Override
public void getSequenceActionList(List<SequenceAction> sequenceActionList) {
for (SequenceAction sa: sequenceActionList) {
Log.d("SA","ID=" + String.valueOf(sa.getSequenceId()) + " AT=" + sa.getActionType() + " EI=" + sa.getExtraInfo());
}
}
结果如下(第一行为ID=0 AT=X0 EI=Y0
,即第一行的ID列为0):
06-17 02:56:47.867 5526-5554/rt_mjt.roomtest D/SA: ID=0 AT=X0 EI=Y0
ID=1 AT=X0 EI=Y0
ID=2 AT=X0 EI=Y0
ID=3 AT=X0 EI=Y0
ID=4 AT=X1 EI=Y1
ID=5 AT=X1 EI=Y1
ID=6 AT=X1 EI=Y1
ID=7 AT=X1 EI=Y1
06-17 02:56:47.868 5526-5554/rt_mjt.roomtest D/SA: ID=8 AT=X2 EI=Y2
ID=9 AT=X2 EI=Y2
ID=10 AT=X2 EI=Y2
ID=11 AT=X2 EI=Y2
ID=12 AT=X3 EI=Y3
ID=13 AT=X3 EI=Y3
ID=14 AT=X3 EI=Y3
ID=15 AT=X3 EI=Y3
ID=16 AT=X4 EI=Y4
06-17 02:56:47.869 5526-5554/rt_mjt.roomtest D/SA: ID=17 AT=X4 EI=Y4
ID=18 AT=X4 EI=Y4
ID=19 AT=X4 EI=Y4
ID=20 AT=X5 EI=Y5
ID=21 AT=X5 EI=Y5
ID=22 AT=X5 EI=Y5
ID=23 AT=X5 EI=Y5
ID=24 AT=X6 EI=Y6
ID=25 AT=X6 EI=Y6
ID=26 AT=X6 EI=Y6
ID=27 AT=X6 EI=Y6
06-17 02:56:47.870 5526-5554/rt_mjt.roomtest D/SA: ID=28 AT=X7 EI=Y7
ID=29 AT=X7 EI=Y7
ID=30 AT=X7 EI=Y7
ID=31 AT=X7 EI=Y7
ID=32 AT=X8 EI=Y8
ID=33 AT=X8 EI=Y8
ID=34 AT=X8 EI=Y8
ID=35 AT=X8 EI=Y8
ID=36 AT=X9 EI=Y9
ID=37 AT=X9 EI=Y9
ID=38 AT=X9 EI=Y9
ID=39 AT=X9 EI=Y9
- 注意,由于多个线程的无序运行,结果可能会出现异常。
addSomeData
方法的使用如下:
private void addSomeData() {
new Thread(new Runnable() {
@Override
public void run() {
SequenceAction sa = new SequenceAction();
for (int i=0; i < 10; i++) {
sa.setSequenceId(0);
sa.setActionType("X" + String.valueOf(i));
sa.setExtraInfo("Y" + String.valueOf(i));
mTestDB.SequenceActionDaoAccess().insertSingleRow(sa);
}
}
}) .start();
}
关于评论的补充:
"我认为你必须在 Room 数据库实例化之前执行清除运行索引的 SQL" - 你是指这样做吗?- ghosh
不一定,但在 Room 打开数据库之前,也就是在尝试对其进行任何操作之前。已添加调用代码(在覆盖的 onStart() 方法中),其中包含一些 Room Db 访问以立即调用 addSomeData。- MikeT
以下是在 RoomDatabase 实例化之后,在访问/打开数据库之前调用 resetSequenceAction 方法的示例(addSomeData 打开已实例化的数据库并插入了 10 行):
@Override
protected void onStart() {
super.onStart();
mTestDB = Room.databaseBuilder(this,TestDatabase.class,TestDatabase.DBNAME).build();
resetSequenceAction();
addSomeData();
addSomeData();
addSomeData();
addSomeData();
listAllRows();