SQLite - ORDER BY RAND()

2021-11-20 00:00:00 sql database mysql sqlite random-access

在 MySQL 中我可以使用 RAND() 函数，在 SQLite 3 中还有其他选择吗?

In MySQL I can use the RAND() function, is there any alternative in SQLite 3?

推荐答案

using random():

SELECT foo FROM bar
  WHERE id >= (abs(random()) % (SELECT max(id) FROM bar))
  LIMIT 1;

<小时>

编辑(按 QOP): 由于 SQLite Autoincremented 列指出:

EDIT (by QOP): Since the docs on SQLite Autoincremented columns states that:

上面描述的普通ROWID选择算法会生成只要您从不使用最大 ROWID 值，您永远不会删除表中的条目最大的 ROWID.如果您曾经删除过行，那么 ROWID 来自创建新行时可能会重复使用以前删除的行.

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows, then ROWIDs from previously deleted rows might be reused when creating new rows.

以上仅当您没有 INTEGER PRIMARY KEY AUTOINCREMENT 列时才正确(它仍然可以与 INTEGER PRIMARY KEY 列一起使用).无论如何，这应该更便携/可靠:

The above is only true if you don't have a INTEGER PRIMARY KEY AUTOINCREMENT column (it will still work fine with INTEGER PRIMARY KEY columns). Anyway, this should be more portable / reliable:

SELECT foo FROM bar
  WHERE _ROWID_ >= (abs(random()) % (SELECT max(_ROWID_) FROM bar))
LIMIT 1;

ROWID、_ROWID_ 和 OID 都是 SQLite 内部行 ID 的别名.

ROWID, _ROWID_ and OID are all aliases for the SQLite internal row id.

相关文章