英文:
SQLite DELETE query freezes on moveToFirst()
问题
我正在尝试在Android应用程序中从SQLite数据库中删除行。
以下是查询和调用它的Java代码：
final Cursor cursor = mDb.rawQuery("DELETE FROM link WHERE version!=? AND sentence IN (SELECT _id FROM sentence WHERE language=?) AND translation IN (SELECT _id FROM sentence WHERE language=?)", new String[]{String.valueOf(versionToConserve), sentenceLanguage, translationLanguage});
cursor.moveToFirst();
cursor.close();
应用程序在cursor.moveToFirst()处冻结。
以下是EXPLAIN QUERY PLAN：
selectid:0, order:0, from:0, detail:SEARCH TABLE link USING INDEX sqlite_autoindex_link_1 (sentence=? AND translation=?),
selectid:0, order:0, from:0, detail:EXECUTE LIST SUBQUERY 0,
selectid:0, order:0, from:0, detail:SCAN TABLE sentence,
selectid:0, order:0, from:0, detail:EXECUTE LIST SUBQUERY 1,
selectid:1, order:0, from:0, detail:SCAN TABLE sentence,
我以为查询可能太慢了，但半个小时过去了，仍然停在这里。
我尝试将DELETE替换为SELECT，它也被冻结。
我单独尝试了内部的SELECT查询，它们完美地工作。
我尝试用(1,2,3)和(4,5,6)替换了内部的SELECT查询，它能工作。
我尝试使用JOIN代替IN (SELECT ...)，但它不接受。它说期望LIMIT、WHERE或其他术语，而不是JOIN。
我不知道如何进一步调查。有任何想法吗？
英文:
I'm trying to delete rows in an SQLite database in an Android application.
Here is the query and the Java code calling it:
final Cursor cursor = mDb.rawQuery("DELETE FROM link WHERE version!=? AND sentence IN (SELECT _id FROM sentence WHERE language=?) AND translation IN (SELECT _id FROM sentence WHERE language=?)", new String[]{String.valueOf(versionToConserve), sentenceLanguage, translationLanguage});
cursor.moveToFirst();
cursor.close();
The application freezes on cursor.moveToFirst().
Here is the EXPLAIN QUERY PLAN:
selectid:0, order:0, from:0, detail:SEARCH TABLE link USING INDEX sqlite_autoindex_link_1 (sentence=? AND translation=?),
selectid:0, order:0, from:0, detail:EXECUTE LIST SUBQUERY 0,
selectid:0, order:0, from:0, detail:SCAN TABLE sentence,
selectid:0, order:0, from:0, detail:EXECUTE LIST SUBQUERY 1,
selectid:1, order:0, from:0, detail:SCAN TABLE sentence,
I thought that maybe the query could be too slow, but after half an hour, it is still stucked here.
I tried to replace DELETE by SELECT, it freezes as well.
I tried the inner SELECT queries alone, they work perfectly.
I tried to replace the inner SELECT queries by (1,2,3) and (4,5,6), it works.
I tried to use JOIN instead of IN (SELECT ...) but it doesn't accept it. It says LIMIT, WHERE or other terms are expected instead of JOIN.
I don't know how to investigate more. Any ideas?
答案1
得分: 1
不要使用rawQuery()来删除行。
方法rawQuery()用于通过SELECT语句返回行，以Cursor的形式。
使用 delete()：
String strWhere = "version <> ? AND " +
"sentence IN (SELECT _id FROM sentence WHERE language = ?) AND " +
"translation IN (SELECT _id FROM sentence WHERE language = ?)";
int rows = mDB.delete(
"link",
strWhere,
new String[]{String.valueOf(versionToConserve), sentenceLanguage, translationLanguage}
);
赋给变量rows的delete()的返回值包含已删除行的数量。
英文:
Don't use rawQuery() to delete rows.<br/>
The method rawQuery() is used to return rows with a SELECT statement, in the form of a Cursor.<br/>
Use delete():
String strWhere = "version <> ? AND " +
"sentence IN (SELECT _id FROM sentence WHERE language = ?) AND " +
"translation IN (SELECT _id FROM sentence WHERE language = ?)";
int rows = mDB.delete(
"link",
strWhere,
new String[]{String.valueOf(versionToConserve), sentenceLanguage, translationLanguage}
);
The returned value of delete() which is assigned to the variable rows contains the number of deleted rows.
答案2
得分: 0
我最终通过这个WHERE子句使它工作起来：
version<>？
AND EXISTS（SELECT 1 FROM sentence WHERE language=? AND sentence=_id）
AND EXISTS（SELECT 1 FROM sentence WHERE language=? AND translation=_id）
英文:
I finally got it to work with this WHERE clause:
version<>?
AND EXISTS (SELECT 1 FROM sentence WHERE language=? AND sentence=_id)
AND EXISTS (SELECT 1 FROM sentence WHERE language=? AND translation=_id)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论