SQLite 删除数据库中最后 25% 的记录

Question

我正在使用 SQLite 数据库来存储来自数据记录器的值。数据记录器最终会填满计算机上所有可用的硬盘空间。我正在寻找一种方法，一旦达到某个限制，就从数据库中删除最后 25% 的日志。

使用以下代码:

$ret = Query( 'SELECT id as last FROM data ORDER BY id desc LIMIT 1 ;' ); 
$last_id = $ret[0]['last'] ; 
$ret = Query( 'SELECT count( * ) as total FROM data' );
$start_id = $last_id - $ret[0]['total'] * 0.75 ; 
Query( 'DELETE FROM data WHERE id < '. round( $start_id, 0 ) );

在数据库旁边创建一个日志文件，它会填满驱动器上的剩余空间，直到脚本失败。

我如何/可以停止创建此日志文件？无论如何将所有三个 SQL 查询合并到一个语句中？

Answer 1

如果日志是问题的唯一原因，您可以尝试让 SQLite 在内存中记录日志，或者将其关闭。

来自 the docs :

PRAGMA journal_mode;
PRAGMA database.journal_mode;
PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | OFF
PRAGMA database.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | OFF

This pragma queries or sets the journal mode for databases associated with the current database connection.

The first two forms of this pragma query the current journaling mode. In the first form, the default journal_mode is returned. The default journaling mode is the mode used by databases added to the connection by subsequent ATTACH statements. The second form returns the current journaling mode for a specific database.

The last two forms change the journaling mode. The 4th form changes the journaling mode for a specific database connection. Use "main" for the main database (the database that was opened by the original sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2() interface call) and use "temp" for database that holds TEMP tables. The 3rd form changes the journaling mode on all databases and it changes the default journaling mode that will be used for new databases added by subsequent ATTACH commands. The new journal mode is returned. If the journal mode could not be changed, the original journal mode is returned.

The DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit. (See the documented titled Atomic Commit In SQLite for additional detail.)

The TRUNCATE journaling mode commits transactions by truncating the rollback journal to zero-length instead of deleting it. On many systems, truncating a file is much faster than deleting the file since the containing directory does not need to be changed.

The PERSIST journaling mode prevents the rollback journal from being deleted at the end of each transaction. Instead, the header of the journal is overwritten with zeros. This will prevent other database connections from rolling the journal back. The PERSIST journaling mode is useful as an optimization on platforms where deleting or truncating a file is much more expensive than overwriting the first block of a file with zeros.

The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.

The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The ROLLBACK command no longer works; it behaves in an undefined way. Applications must avoid using the ROLLBACK command when the journal mode is OFF. If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt.

Note that the journal_mode for an in-memory database is either MEMORY or OFF and can not be changed to a different value. An attempt to change the journal_mode of an in-memory database to any setting other than MEMORY or OFF is ignored. Note also that the journal_mode cannot be changed while a transaction is active.