SQLite RANDOM () allocation

Question

Does SQLite support serialization of the RANDOM() functions in the same way that MySQL does with RAND() ?

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

From the MySQL manual on RAND(N) :

If the constant integer argument N is equal to the specified, it is used as a seed value, which creates a repeatability sequence of column values. in the following example, note that the sequence of values ​​created by RAND (3) is the same place where this occurs.

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

Answer 1

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

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

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

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

Answer 2

Take a look at sqlite3_randomness() :

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 maximum possible ROWID. PRNG is also used for the built-in functions random () and randomblob () SQL.

...

When this procedure was first called (inside or in the application), the PRNG was seeded using randomness obtained from the xRandomness method of the sqlite3_vfs object by default . In all subsequent calls, pseudo-randomness is created internally and without invoking the sqlite3_vfs xRandomness method.

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