ORDER BY random（）和SQLITE中的种子 [英] ORDER BY random() with seed in SQLITE

问题描述

我想为随机集实现分页

Select * from Animals ORDER BY random(SEED) LIMIT 100 OFFSET 50  

我试图将int设置为某个整数和某些断裂。不起作用

I tried to set int to some integer and to some fracture. Doesn't work

如何在sqlite中随机播种？

How do I seed random in sqlite?

我在这里抓住机会投票，因为类似的问题已经存在 - 播种SQLite RANDOM（）。我只是没有得到PHP解决方案。

I am tacking a chance here with down votes because similar question already exist - Seeding SQLite RANDOM(). I just didn't get the php solution.

推荐答案

简答：

你不能。 SQLite的random（）函数不支持种子值。

You can't. SQLite's random() function does not support a seed value.

答案不是那么简短：

检查SQLite的 func.c 表明random（）的定义没有任何参数..

Checking SQLite's func.c shows that random() is defined without any parameters..

VFUNCTION(random,            0, 0, 0, randomFunc       ),

..而这个randomFunc（）只调用sqlite3_randomness（）（再次没有任何显式的种子值）来获得 sizeof的随机值（sqlite_int64） bytes。

..and this randomFunc() just calls sqlite3_randomness() (again without any explicit seed value) to obtain a random value of sizeof(sqlite_int64) bytes.

在内部，sqlite3_randomness（）的实现（参见 random.c ）将在第一次使用从操作系统获得的随机种子值时设置RC4伪随机数生成器：

Internally, the implementation of sqlite3_randomness() (see random.c) will set up the RC4 pseudo-random number generator the first time it is used with random seed values obtained from the OS:

  /* Initialize the state of the random number generator once,
  ** the first time this routine is called.  The seed value does
  ** not need to contain a lot of randomness since we are not
  ** trying to do secure encryption or anything like that...
  **
  ** [..]
  */
  if( !wsdPrng.isInit ){
      [..]
      sqlite3OsRandomness(sqlite3_vfs_find(0), 256, k);
      [..]
      wsdPrng.isInit = 1;
  }

实际上，SQLite的单元测试函数本身只是在全局sqlite3Prng上使用memcpy（）结构在测试运行期间保存或恢复PRNG的状态。

Actually, SQLite's unit test functions themselves just use memcpy() on the global sqlite3Prng struct to save or restore the state of the PRNG during test runs.

所以，除非你愿意做一些奇怪的事情（比如创建一个连续数字的临时表） 1..max（动物）），将那些周围的人洗牌并用它们从你的动物表中选择'随机播种的'RowIds）我想你运气不好。

So, unless you're willing to do something weird (like create a temporary table of consecutive numbers (1..max(Animals)), shuffle those around and use them to select 'random-seeded' RowIds from your Animals table) I suppose you're out of luck.

这篇关于ORDER BY random（）和SQLITE中的种子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！