MySQL SQLite - ORDER BY RAND()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1253561/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite - ORDER BY RAND()
提问by Alix Axel
In MySQL I can use the RAND() function, is there any alternative in SQLite 3?
在 MySQL 中我可以使用 RAND() 函数，在 SQLite 3 中还有其他选择吗？
采纳答案by dfa
using random():
使用随机（）：
SELECT foo FROM bar
WHERE id >= (abs(random()) % (SELECT max(id) FROM bar))
LIMIT 1;
EDIT (by QOP):Since the docs on SQLite Autoincremented columns states that:
编辑（按 QOP）：由于SQLite Autoincremented 列上的文档指出：
The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDsas 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.
上面描述的普通 ROWID 选择算法将生成单调递增的唯一 ROWID，只要您从不使用最大 ROWID 值并且从不删除表中具有最大 ROWID 的条目。如果您曾经删除过行，那么在创建新行时可能会重用先前删除的行中的 ROWID。
The above is only true if you don't have a INTEGER PRIMARY KEY AUTOINCREMENTcolumn (it will still work fine with INTEGER PRIMARY KEYcolumns). Anyway, this should be more portable / reliable:
以上仅当您没有INTEGER PRIMARY KEY AUTOINCREMENT列时才正确（它仍然可以与INTEGER PRIMARY KEY列一起正常工作）。无论如何，这应该更便携/可靠：
SELECT foo FROM bar
WHERE _ROWID_ >= (abs(random()) % (SELECT max(_ROWID_) FROM bar))
LIMIT 1;
ROWID, _ROWID_and OIDare all aliases for the SQLite internal row id.
ROWID,_ROWID_和OID都是 SQLite 内部行 ID 的别名。
回答by avnic
SELECT * FROM table ORDER BY RANDOM() LIMIT 1;
回答by Alix Axel
Solved:
解决了：
SELECT * FROM table ORDER BY RANDOM() LIMIT 1;
回答by Ali
For a much better performance use this in SQLite:
为了获得更好的性能，请在 SQLite 中使用它：
SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x)
This is also applicable to MySQL. This runs faster because SQL engines first load projected fields of rows to memory then sort them, here we just load and random sort the id field of rows, then we get X of them, and find the whole rows of these X ids which is by default indexed.
这也适用于 MySQL。这运行得更快，因为SQL 引擎首先将行的投影字段加载到内存然后对它们进行排序，这里我们只是加载并随机排序行的 id 字段，然后我们得到其中的 X 个，并找到这些 X id 的整行默认索引。
