I have two tables. In one table, the user id , in another, the user id , which the random function should not call. It is necessary to derive 3 random non repeating numbers from the $allowed variable.

 $banned = "SELECT id FROM admin"; $allowed = "SELECT id FROM 10a WHERE id NOT IN (". $banned .")";
  • ORDER BY RAND ()? - Maxim Stepanov
  • If my answer helped you, mark it, please, as adopted by pressing v under the tally counter - Sergey Nudnov

1 answer 1

The simplest option, which judging by the name of table 10a (school?), Will suit you:

 SELECT id FROM 10a WHERE id NOT IN (SELECT id FROM admin) ORDER BY RAND() LIMIT 3

The disadvantage of this option is a logarithmic slowdown with an increase in the number of rows in table 10a , since ORDER BY RAND() causes scanning and then sorting the entire table as a whole - and without using indexes.

Here and here it is all pretty well written.

The variant for large tables, which seems to me preferable, is inserting an additional indexed float of the rnd column into the table, in which the result of the RAND() function is written to when creating the row. In this case, randomization is performed at the stage of filling the table, and when sampling we randomly determine the initial value of the rnd column and read the required number of rows sorted by rnd .

An example on db-fiddle . Schemes of tables and query - below:

 CREATE TABLE 10a ( id INT NOT NULL AUTO_INCREMENT, name text, rnd float, PRIMARY KEY (id) ); CREATE TABLE admin ( id INT ); CREATE TRIGGER 10a_bi BEFORE INSERT ON 10a FOR EACH ROW SET NEW.rnd = RAND(); CREATE INDEX rnd_idx ON 10a(rnd);

Note that in the query 2 times the number 3 occurs - the number of returned values. This is done to ensure that “losers” with the 3 largest rnd values ​​have an equally likely chance of getting into the issue, like all the others.

 SELECT r.* FROM ( SELECT RAND() * ( SELECT rnd FROM 10a WHERE id NOT IN (SELECT id FROM admin) ORDER BY rnd DESC LIMIT 1 OFFSET 3 ) start ) init JOIN 10a r WHERE r.rnd > init.start AND id NOT IN (SELECT id FROM admin) ORDER BY r.rnd LIMIT 3;