Seeding SQLite RANDOM()

Question

Does SQLite support seeding the RANDOM() function the same way MySQL does with RAND()?

$query = "SELECT * FROM table ORDER BY RAND(" . date('Ymd') . ") LIMIT 1;";

From the MySQL Manual about RAND(N):

If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values. In the following example, note that the sequences of values produced by RAND(3) is the same both places where it occurs.

If not, is there any way to archive the same effect using only one query?

Answer 1

Have a look at the sqlite3_randomness() function:

SQLite contains a high-quality pseudo-random number generator (PRNG) used to select random ROWIDs when inserting new records into a table that already uses the largest possible ROWID. The PRNG is also used for the build-in random() and randomblob() SQL functions.

...

The first time this routine is invoked (either internally or by the application) the PRNG is seeded using randomness obtained from the xRandomness method of the default sqlite3_vfs object. On all subsequent invocations, the pseudo-randomness is generated internally and without recourse to the sqlite3_vfs xRandomness method.

Looking at the source of this xRandomness method, you can see that it reads from /dev/urandom on Unix. On Windows, it just returns the return values of some time functions. So it seems that your only option is to start hacking on the SQLite source code.

Answer 2

If you need a pseudo-random order, you can do something like this (PHP):

$seed = md5(mt_rand());
$prng = ('0.' . str_replace(['0', 'a', 'b', 'c', 'd', 'e', 'f'], ['7', '3', '1', '5', '9', '8', '4'], $seed )) * 1;
$query = 'SELECT id, name FROM table ORDER BY (substr(id * ' . $prng . ', length(id) + 2))';

Plus, you can set $seed to the predefined value and always get same results.

I've learned this trick from my colleague http://steamcooker.blogspot.com/