Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
401 views
in Technique[技术] by (71.8m points)

php - ORDER BY random() with seed in SQLITE

I would like to implement paging for a random set

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

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

How do I seed random in sqlite?

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Short answer:

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

Not so short answer:

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

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

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

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;
  }

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.

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...