SQLite changes() function reports 0 rows changed i

问题:

I've recently found the CHANGES() function 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?

回答1:

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.

回答2:

From: the SQLite web site

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. Prior to SQLite version 3.6.5, the truncate optimization also meant that the sqlite3_changes() and sqlite3_total_changes() interfaces and the count_changes pragma will not actually return the number of deleted rows. That problem has been fixed as of version 3.6.5.

The truncate optimization can be permanently disabled for all queries by recompiling SQLite with the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.

The truncate optimization can also be disabled at runtime using the sqlite3_set_authorizer() interface. If an authorizer callback returns SQLITE_IGNORE for an SQLITE_DELETE action code, then the DELETE operation will proceed but the truncate optimization will be bypassed and rows will be deleted one by one.