SQLite的变化（）编译报道0行某些DELETE语句改变 [英] SQLite changes() pragma reports 0 rows changed in certain delete statements

问题描述

我最近找到了改变（）编译可用的 SQLite的。我在做类似的东西在我的Andr​​oid code以下内：

  db.execSQL（的SQLStatement，argumentArray）;
INT结果;
SQLiteStatement语句= db.compileStatement（选择改变（））;
尝试{
    返回stmt.simpleQueryForLong（）;
} {最后
    stmt.close（）;
}
 

我所看到的，我得到良好的数据对所有语句，如：

 更新`footable` SET`stuff` ='fepojefpjo（返回1行的更新）
DROP TABLE`footable`（返回2行下降）
DELETE FROM`footable` WHERE`id` IN（？，？）（返回2行删除）
 

不过，我总是得到0行改变时，我发出下面的语句 - 即使有，肯定是被删除的行：

  DELETE FROM`footable`（总是返回0）
 

由于我从表中删除了一切，我不知道是否SQLite是做一些在幕后截断操作。如果我仅仅增加一个垃圾 1 来返回的行权数量的语句的结束。

  DELETE FROM`footable` WHERE 1（作品）
 

问题：

1. 任何人都可以证实这种行为？这是可以预料的？


2. 为SQLite截断表吗？


3. 除了计数行的前的的删除，有没有什么办法来解决此问题？

解决方案

我觉得这是最有可能是由于所谓的截断优化（我说的最有可能的，因为我不知道你正在使用的SQLite的版本） 。如果没有 WHERE 子句中，使用的SQLite优化删除整个表的内容，而不必单独访问表的每一行。请参阅参考文档的DELETE 。

请注意，由于3.6.5这种行为是固定的。

I've recently found the CHANGES() pragma available inside of SQLite. I'm doing something like the following inside of my Android code:

db.execSQL(sqlStatement, argumentArray);
int result;
SQLiteStatement stmt = db.compileStatement("SELECT CHANGES()");
try {
    return stmt.simpleQueryForLong();
} finally {
    stmt.close();
}

What I'm seeing that I get good data for all statements such as:

UPDATE `footable` SET `stuff` = 'fepojefpjo'   (returns 1 row updated)
DROP TABLE `footable`                          (returns 2 rows dropped)
DELETE FROM `footable` WHERE `id` IN (?,?)     (returns 2 rows deleted)

But I always get 0 rows changed when I issue the following statement -- even if there are definitely rows that are deleted:

DELETE FROM `footable`                         (always returns 0)

Since I'm deleting everything from the table, I wonder if SQLite is doing some sort of truncation operation underneath the covers. If I just adding a junk WHERE 1 to the end of the statement it returns the right number of rows.

DELETE FROM `footable` WHERE 1                 (works)

Questions:

1. Can anyone confirm this behavior? Is this to be expected?
2. Is SQLite truncating the table here?
3. Aside from counting the rows before the DELETE, is there any way to work around this?

解决方案

I think this is most likely due to the so-called Truncate Optimization (I say most likely because I'm not sure which version of SQLite you are using). When there is no WHERE clause, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. See the reference docs for DELETE.

Note that this behavior is fixed since 3.6.5.

这篇关于SQLite的变化（）编译报道0行某些DELETE语句改变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！